hi Friends,
I wrote my Tax report query, in this report i am getting consolidated summary but,
i need project code wise summarys at a time(project code based my branches defined here)
Please go through my query once.
Declare @FDate as datetime Declare @TDate as datetime set @fdate='20160601' set @Tdate='20160630' select sum(PALLexmpt) 'PALLExmpt', sum(PALLVAT5) PALLVAT5, sum([PALLVAT5%]) 'PALLVAT5%', sum(PALLVAT55) PALLVAT55, sum([PALLVAT55%]) 'PALLVAT55%', sum(PALLVAT145) PALLVAT145, sum([PALLVAT145%]) 'PALLVAT145%', sum(PALLCST5) PALLCST5, sum([PALLCST5%]) 'PALLCST5%' , sum(PALLCST2) PALLCST2, sum([PALLCST2%]) 'PALLCST2%', sum(PALLCs14) PALLCs14, sum([PALLCs14%]) 'PALLCs14%', sum(PALL_ST) PALL_ST, sum([PALL_ST%]) 'PALL_ST%', sum(PALL_SB) PALL_SB, sum([PALL_SB%]) 'PALL_SB%', sum(PALL_KK) PALL_KK, sum([PALL_KK%]) 'PALL_KK%', sum(SALLExmpt) 'SALLExmpt', sum(SALLVAT5) 'SALLVAT5', sum([SALLVAT5%]) 'SALLVAT5%', sum(SALLVAT55) 'SALLVAT55', sum([SALLVAT55%]) 'SALLVAT55%', sum(SALLVAT145) 'SALLVAT145', sum([SALLVAT145%]) 'SALLVAT145%', sum(SALLCST5) SALLCST5, sum(SALLCST5) 'SALLCST5%', sum(SALLCST2) SALLCST2, sum([SALLCST2%]) 'SALLCST2%', sum(SALLCs14) 'SALLCs14', sum([SALLCs14%]) 'SALLCs14%', sum(SALL_ST) 'SALL_ST', sum([SALL_ST%]) 'SALL_ST%', sum(SALL_SB) 'SALL_SB', sum([SALL_SB%]) 'SALL_SB%', sum(SALL_KK)'SALL_KK', sum([SALL_KK%]) 'SALL_KK%' from ( Select sum(ALLexmpt) 'PALLExmpt', sum(ALLVAT5) PALLVAT5, sum([ALLVAT5%]) 'PALLVAT5%', sum(ALLVAT55) PALLVAT55, sum([ALLVAT55%]) 'PALLVAT55%', sum(ALLVAT145) PALLVAT145, sum([ALLVAT145%]) 'PALLVAT145%', sum(ALLCST5) PALLCST5, sum([ALLCST5%]) 'PALLCST5%' , sum(ALLCST2) PALLCST2, sum([ALLCST2%]) 'PALLCST2%', sum(ALLCs14) PALLCs14, sum([ALLCs14%]) 'PALLCs14%', sum(ALL_ST) PALL_ST, sum([ALL_ST%]) 'PALL_ST%', sum(ALL_SB) PALL_SB, sum([ALL_SB%]) 'PALL_SB%', sum(ALL_KK) PALL_KK, sum([ALL_KK%]) 'PALL_KK%', 0 'SALLExmpt', 0 'SALLVAT5', 0 'SALLVAT5%', 0 'SALLVAT55', 0 'SALLVAT55%', 0 'SALLVAT145', 0 'SALLVAT145%', 0 SALLCST5, 0 'SALLCST5%', 0 SALLCST2, 0 'SALLCST2%', 0 'SALLCs14', 0 'SALLCs14%', 0 'SALL_ST', 0 'SALL_ST%', 0 'SALL_SB', 0 'SALL_SB%', 0 'SALL_KK', 0 'SALL_KK%' from (SELECT T1.DocDate, (select isnull(Sum(C.BaseSum ),0) from PCH4 C where C.docentry =T1.docentry and (C.TaxRate =0) and c.staType=1 ) as 'ALLExmpt', (select isnull(Sum(C.BaseSum ),0) from PCH4 C where C.docentry =T1.docentry and (C.TaxRate =5) and c.staType=1 ) as 'ALLVAT5', (select isnull(Sum(C.Taxsum),0) from PCH4 C where C.docentry =T1.docentry and (C.TaxRate =5) and c.staType=1 ) as 'ALLVAT5%', (select isnull(Sum(C.BaseSum ),0) from PCH4 C where C.docentry =T1.docentry and (C.TaxRate =5.5) and c.staType=1 ) as 'ALLVAT55', (select isnull(Sum(C.Taxsum),0) from PCH4 C where C.docentry =T1.docentry and (C.TaxRate =5.5) and c.staType=1 ) as 'ALLVAT55%', (select isnull(Sum(C.BaseSum ),0) from PCH4 C where C.docentry =T1.docentry and (C.TaxRate =14.5) and c.staType=1 ) as 'ALLVAT145', (select isnull(Sum(C.Taxsum),0) from PCH4 C where C.docentry =T1.docentry and (C.TaxRate =14.5) and c.staType=1) as 'ALLVAT145%', (select isnull(Sum(C.BaseSum ),0) from PCH4 C where C.docentry =T1.docentry and (C.TaxRate =5) and c.staType=4 ) as 'ALLCST5', (select isnull(Sum(C.Taxsum),0) from PCH4 C where C.docentry =T1.docentry and (C.TaxRate =5) and c.staType=4) as 'ALLCST5%', (select isnull(Sum(C.BaseSum ),0) from PCH4 C where C.docentry =T1.docentry and (C.TaxRate =2) and c.staType=4 ) as 'ALLCST2', (select isnull(Sum(C.Taxsum),0) from PCH4 C where C.docentry =T1.docentry and (C.TaxRate =2) and c.staType=4 ) as 'ALLCST2%', (select isnull(Sum(C.BaseSum ),0) from PCH4 C where C.docentry =T1.docentry and (C.TaxRate =14.5) and c.staType=4) as 'ALLCs14', (select isnull(Sum(C.Taxsum),0) from PCH4 C where C.docentry =T1.docentry and (C.TaxRate =14.5) and c.staType=4) as 'ALLCs14%', (select isnull(Sum(C.BaseSum ),0) from PCH4 C where C.docentry =T1.docentry and (C.TaxRate =14) and c.staType in (5)) as 'ALL_ST', (select isnull(Sum(C.Taxsum),0) from PCH4 C where C.docentry =T1.docentry and (C.TaxRate =14) and c.staType in (5)) as 'ALL_ST%', (select isnull(Sum(C.BaseSum ),0) from PCH4 C where C.docentry =T1.docentry and (C.TaxRate =0.5) and c.staType in (7)) as 'ALL_SB', (select isnull(Sum(C.Taxsum),0) from PCH4 C where C.docentry =T1.docentry and (C.TaxRate =0.5) and c.staType in (7)) as 'ALL_SB%', (select isnull(Sum(C.BaseSum ),0) from PCH4 C where C.docentry =T1.docentry and (C.TaxRate =0.5) and c.staType in (8)) as 'ALL_KK', (select isnull(Sum(C.Taxsum),0) from PCH4 C where C.docentry =T1.docentry and (C.TaxRate =0.5) and c.staType in (8)) as 'ALL_KK%' FROM PCH1 T0 INNER JOIN OPCH T1 ON T0.DocEntry = T1.DocEntry where T1.TaxDate>=@Fdate and T1.TaxDate <=@TDate Group by T1.DocDate,T1.DocEntry Union All SELECT T1.DocDate, (select isnull(Sum(C.BaseSum ),0)*-1 from RPC4 C where C.docentry =T1.docentry and (C.TaxRate =0) and c.staType=1 ) as 'ALLExmpt', (select isnull(Sum(C.BaseSum ),0)*-1 from RPC4 C where C.docentry =T1.docentry and (C.TaxRate =5) and c.staType=1 ) as 'ALLVAT5', (select isnull(Sum(C.Taxsum),0)*-1 from RPC4 C where C.docentry =T1.docentry and (C.TaxRate =5) and c.staType=1 ) as 'ALLVAT5%', (select isnull(Sum(C.BaseSum ),0)*-1 from RPC4 C where C.docentry =T1.docentry and (C.TaxRate =5.5) and c.staType=1 ) as 'ALLVAT55', (select isnull(Sum(C.Taxsum),0)*-1 from RPC4 C where C.docentry =T1.docentry and (C.TaxRate =5.5) and c.staType=1 ) as 'ALLVAT55%', (select isnull(Sum(C.BaseSum ),0)*-1 from RPC4 C where C.docentry =T1.docentry and (C.TaxRate =14.5) and c.staType=1 ) as 'ALLVAT145', (select isnull(Sum(C.Taxsum),0)*-1 from RPC4 C where C.docentry =T1.docentry and (C.TaxRate =14.5) and c.staType=1) as 'ALLVAT145%', (select isnull(Sum(C.BaseSum ),0)*-1 from RPC4 C where C.docentry =T1.docentry and (C.TaxRate =5) and c.staType=4 ) as 'ALLCST5', (select isnull(Sum(C.Taxsum),0)*-1 from RPC4 C where C.docentry =T1.docentry and (C.TaxRate =5) and c.staType=4) as 'ALLCST5%', (select isnull(Sum(C.BaseSum ),0)*-1 from RPC4 C where C.docentry =T1.docentry and (C.TaxRate =2) and c.staType=4 ) as 'ALLCST2', (select isnull(Sum(C.Taxsum),0)*-1 from RPC4 C where C.docentry =T1.docentry and (C.TaxRate =2) and c.staType=4 ) as 'ALLCST2%', (select isnull(Sum(C.BaseSum ),0)*-1 from RPC4 C where C.docentry =T1.docentry and (C.TaxRate =14.5) and c.staType=4) as 'ALLCs14', (select isnull(Sum(C.Taxsum),0)*-1 from RPC4 C where C.docentry =T1.docentry and (C.TaxRate =14.5) and c.staType=4) as 'ALLCs14%', (select isnull(Sum(C.BaseSum ),0)*-1 from RPC4 C where C.docentry =T1.docentry and (C.TaxRate =14) and c.staType in (5)) as 'ALL_ST', (select isnull(Sum(C.Taxsum),0)*-1 from RPC4 C where C.docentry =T1.docentry and (C.TaxRate =14) and c.staType in (5)) as 'ALL_ST%', (select isnull(Sum(C.BaseSum ),0)*-1 from RPC4 C where C.docentry =T1.docentry and (C.TaxRate =0.5) and c.staType in (7)) as 'ALL_SB', (select isnull(Sum(C.Taxsum),0)*-1 from RPC4 C where C.docentry =T1.docentry and (C.TaxRate =0.5) and c.staType in (7)) as 'ALL_SB%', (select isnull(Sum(C.BaseSum ),0)*-1 from RPC4 C where C.docentry =T1.docentry and (C.TaxRate =0.5) and c.staType in (8)) as 'ALL_KK', (select isnull(Sum(C.Taxsum),0) *-1from RPC4 C where C.docentry =T1.docentry and (C.TaxRate =0.5) and c.staType in (8)) as 'ALL_KK%' FROM RPC1 T0 INNER JOIN ORPC T1 ON T0.DocEntry = T1.DocEntry where T1.TaxDate>=@Fdate and T1.TaxDate <=@TDate Group by T1.DocDate,T1.DocEntry ) as ss Union All Select 0 'PALLExmpt', 0 PALLVAT5, 0 'PALLVAT5%', 0 PALLVAT55, 0 'PALLVAT55%', 0 PALLVAT145, 0 'PALLVAT145%', 0 PALLCST5, 0 'PALLCST5%' , 0 PALLCST2, 0 'PALLCST2%', 0 PALLCs14, 0 'PALLCs14%', 0 PALL_ST, 0 'PALL_ST%', 0 PALL_SB, 0 'PALL_SB%', 0 PALL_KK, 0 'PALL_KK%', sum(ALLexmpt) 'SALLExmpt', sum(ALLVAT5) 'SALLVAT5', sum([ALLVAT5%]) 'SALLVAT5%', sum(ALLVAT55) 'SALLVAT55', sum([ALLVAT55%]) 'SALLVAT55%', sum(ALLVAT145) 'SALLVAT145', sum([ALLVAT145%]) 'SALLVAT145%', sum(ALLCST5) SALLCST5, sum([ALLCST5%]) 'SALLCST5%', sum(ALLCST2) SALLCST2, sum([ALLCST2%]) 'SALLCST2%', sum(ALLCs14) 'SALLCs14', sum([ALLCs14%]) 'SALLCs14%', sum(ALL_ST) 'SALL_ST', sum([ALL_ST%]) 'SALL_ST%', sum(ALL_SB) 'SALL_SB', sum([ALL_SB%]) 'SALL_SB%', sum(ALL_KK) 'SALL_KK', sum([ALL_KK%]) 'SALL_KK%' from ( SELECT T1.DocDate, (select isnull(Sum(C.BaseSum ),0) from INV4 C where C.docentry =T1.docentry and (C.TaxRate =0) and c.staType=1 ) as 'ALLExmpt', (select isnull(Sum(C.BaseSum ),0) from INV4 C where C.docentry =T1.docentry and (C.TaxRate =5) and c.staType=1 ) as 'ALLVAT5', (select isnull(Sum(C.Taxsum),0) from INV4 C where C.docentry =T1.docentry and (C.TaxRate =5) and c.staType=1 ) as 'ALLVAT5%', (select isnull(Sum(C.BaseSum ),0) from INV4 C where C.docentry =T1.docentry and (C.TaxRate =5.5) and c.staType=1 ) as 'ALLVAT55', (select isnull(Sum(C.Taxsum),0) from INV4 C where C.docentry =T1.docentry and (C.TaxRate =5.5) and c.staType=1 ) as 'ALLVAT55%', (select isnull(Sum(C.BaseSum ),0) from INV4 C where C.docentry =T1.docentry and (C.TaxRate =14.5) and c.staType=1 ) as 'ALLVAT145', (select isnull(Sum(C.Taxsum),0) from INV4 C where C.docentry =T1.docentry and (C.TaxRate =14.5) and c.staType=1) as 'ALLVAT145%', (select isnull(Sum(C.BaseSum ),0) from INV4 C where C.docentry =T1.docentry and (C.TaxRate =5) and c.staType=4 ) as 'ALLCST5', (select isnull(Sum(C.Taxsum),0) from INV4 C where C.docentry =T1.docentry and (C.TaxRate =5) and c.staType=4) as 'ALLCST5%', (select isnull(Sum(C.BaseSum ),0) from INV4 C where C.docentry =T1.docentry and (C.TaxRate =2) and c.staType=4 ) as 'ALLCST2', (select isnull(Sum(C.Taxsum),0) from INV4 C where C.docentry =T1.docentry and (C.TaxRate =2) and c.staType=4 ) as 'ALLCST2%', (select isnull(Sum(C.BaseSum ),0) from INV4 C where C.docentry =T1.docentry and (C.TaxRate =14.5) and c.staType=4) as 'ALLCs14', (select isnull(Sum(C.Taxsum),0) from INV4 C where C.docentry =T1.docentry and (C.TaxRate =14.5) and c.staType=4) as 'ALLCs14%', (select isnull(Sum(C.BaseSum ),0) from INV4 C where C.docentry =T1.docentry and (C.TaxRate =14) and c.staType in (5)) as 'ALL_ST', (select isnull(Sum(C.Taxsum),0) from INV4 C where C.docentry =T1.docentry and (C.TaxRate =14) and c.staType in (5)) as 'ALL_ST%', (select isnull(Sum(C.BaseSum ),0) from INV4 C where C.docentry =T1.docentry and (C.TaxRate =0.5) and c.staType in (7)) as 'ALL_SB', (select isnull(Sum(C.Taxsum),0) from INV4 C where C.docentry =T1.docentry and (C.TaxRate =0.5) and c.staType in (7)) as 'ALL_SB%', (select isnull(Sum(C.BaseSum ),0) from INV4 C where C.docentry =T1.docentry and (C.TaxRate =0.5) and c.staType in (8)) as 'ALL_KK', (select isnull(Sum(C.Taxsum),0) from INV4 C where C.docentry =T1.docentry and (C.TaxRate =0.5) and c.staType in (8)) as 'ALL_KK%' FROM INV1 T0 INNER JOIN OINV T1 ON T0.DocEntry = T1.DocEntry where T1.DocDate>=@Fdate and T1.DocDate <=@TDate Group by T1.DocDate,T1.DocEntry Union All SELECT T1.DocDate, (select isnull(Sum(C.BaseSum ),0)*-1 from RIN4 C where C.docentry =T1.docentry and (C.TaxRate =0) and c.staType=1 ) as 'ALLExmpt', (select isnull(Sum(C.BaseSum ),0)*-1 from RIN4 C where C.docentry =T1.docentry and (C.TaxRate =5) and c.staType=1 ) as 'ALLVAT5', (select isnull(Sum(C.Taxsum),0)*-1 from RIN4 C where C.docentry =T1.docentry and (C.TaxRate =5) and c.staType=1 ) as 'ALLVAT5%', (select isnull(Sum(C.BaseSum ),0)*-1 from RIN4 C where C.docentry =T1.docentry and (C.TaxRate =5.5) and c.staType=1 ) as 'ALLVAT55', (select isnull(Sum(C.Taxsum),0)*-1 from RIN4 C where C.docentry =T1.docentry and (C.TaxRate =5.5) and c.staType=1 ) as 'ALLVAT55%', (select isnull(Sum(C.BaseSum ),0)*-1 from RIN4 C where C.docentry =T1.docentry and (C.TaxRate =14.5) and c.staType=1 ) as 'ALLVAT145', (select isnull(Sum(C.Taxsum),0)*-1 from RIN4 C where C.docentry =T1.docentry and (C.TaxRate =14.5) and c.staType=1) as 'ALLVAT145%', (select isnull(Sum(C.BaseSum ),0)*-1 from RIN4 C where C.docentry =T1.docentry and (C.TaxRate =5) and c.staType=4 ) as 'ALLCST5', (select isnull(Sum(C.Taxsum),0)*-1 from RIN4 C where C.docentry =T1.docentry and (C.TaxRate =5) and c.staType=4) as 'ALLCST5%', (select isnull(Sum(C.BaseSum ),0)*-1 from RIN4 C where C.docentry =T1.docentry and (C.TaxRate =2) and c.staType=4 ) as 'ALLCST2', (select isnull(Sum(C.Taxsum),0)*-1 from RIN4 C where C.docentry =T1.docentry and (C.TaxRate =2) and c.staType=4 ) as 'ALLCST2%', (select isnull(Sum(C.BaseSum ),0)*-1 from RIN4 C where C.docentry =T1.docentry and (C.TaxRate =14.5) and c.staType=4) as 'ALLCs14', (select isnull(Sum(C.Taxsum),0)*-1 from RIN4 C where C.docentry =T1.docentry and (C.TaxRate =14.5) and c.staType=4) as 'ALLCs14%', (select isnull(Sum(C.BaseSum ),0)*-1 from RIN4 C where C.docentry =T1.docentry and (C.TaxRate =14) and c.staType in (5)) as 'ALL_ST', (select isnull(Sum(C.Taxsum),0)*-1 from RIN4 C where C.docentry =T1.docentry and (C.TaxRate =14) and c.staType in (5)) as 'ALL_ST%', (select isnull(Sum(C.BaseSum ),0)*-1 from RIN4 C where C.docentry =T1.docentry and (C.TaxRate =0.5) and c.staType in (7)) as 'ALL_SB', (select isnull(Sum(C.Taxsum),0)*-1 from RIN4 C where C.docentry =T1.docentry and (C.TaxRate =0.5) and c.staType in (7)) as 'ALL_SB%', (select isnull(Sum(C.BaseSum ),0)*-1 from RIN4 C where C.docentry =T1.docentry and (C.TaxRate =0.5) and c.staType in (8)) as 'ALL_KK', (select isnull(Sum(C.Taxsum),0)*-1 from RIN4 C where C.docentry =T1.docentry and (C.TaxRate =0.5) and c.staType in (8)) as 'ALL_KK%' FROM RIN1 T0 INNER JOIN ORIN T1 ON T0.DocEntry = T1.DocEntry where T1.DocDate>=@Fdate and T1.DocDate <=@TDate Group by T1.DocDate,T1.DocEntry ) as ss ) as pp
Add comment