Skip to Content
0
Former Member
Jun 10, 2016 at 09:54 AM

How to make one from two query

14 Views

here its my query i want to make as one query

Alter proc [dbo].[@SMS_RPT_Service_Tax_Calculation_Inward]

(

@FromDate Date,

@ToDate Date

)

as

begin

select A.*,(A.[UNIT-I]+A.[UNIT-II]+A.[UNIT-IV]+A.[UNIT-UTK]) [total] from

(

Select T2.FormatCode,T1.AcctCode,T2.AcctName,

Case when T1.LocCode='3' then sum(T1.linetotal) end [UNIT-I],

Case when T1.LocCode='2' then sum(T1.linetotal) end [UNIT-II],

Case when T1.LocCode='4' then sum(T1.linetotal) end [UNIT-IV],

Case when T1.LocCode='5' then sum(T1.linetotal) end [UNIT-UTK]

From OPCH T0

inner join PCH1 T1 on T1.Docentry=T0.DocEntry

INNER JOIN OACT T2 ON T2.[AcctCode] = T1.[AcctCode]

inner join OLCT T3 on T3.Code=T1.LocCode

where T0.DocEntry=T1.DocEntry AND T2.FORMATCODE LIKE'5110190%' and TaxDate between @FromDate and @ToDate

GROUP BY T1.LocCode,T2.FormatCode,T1.AcctCode,T2.AcctName

)A end

---------------------------------------------------------------------------------------------------

Alter proc [dbo].[@SMS_RPT_Service_Tax_Calculation_Inward]

(

@FromDate Date,

@ToDate Date

)

as

begin

select A.*,(A.[UNIT-I]+A.[UNIT-II]+A.[UNIT-IV]+A.[UNIT-UTK]) [total] from

(

Select T2.FormatCode,T1.AcctCode,T2.AcctName,

Case when T1.LocCode='3' then sum(T1.linetotal) end [UNIT-I],

Case when T1.LocCode='2' then sum(T1.linetotal) end [UNIT-II],

Case when T1.LocCode='4' then sum(T1.linetotal) end [UNIT-IV],

Case when T1.LocCode='5' then sum(T1.linetotal) end [UNIT-UTK]

From OPCH T0

inner join PCH1 T1 on T1.Docentry=T0.DocEntry

INNER JOIN OACT T2 ON T2.[AcctCode] = T1.[AcctCode]

inner join OLCT T3 on T3.Code=T1.LocCode

where T0.DocEntry=T1.DocEntry AND T2.FORMATCODE LIKE'5110180%' and TaxDate between @FromDate and @ToDate

GROUP BY T1.LocCode,T2.FormatCode,T1.AcctCode,T2.AcctName

)A end