Dear Experts,
The below query gives expenses of all G/L accounts based on location. Need to exclude the canceled entries from the query.
/*Select Y.Refdate from ojdt y*/ Declare @FromDate as Date Set @FromDate = '20161001' /*Select Y.Refdate from ojdt y*/ Declare @ToDate as Date Set @ToDate = '20161010' select Y.Number,Y.Account,Y.AcctName,isnull(SUM(Y.Pat),0) 'Pat',isnull(SUM(Y.Kor),0)'Kor' ,isnull(SUM(Y.Kur),0)'Kur' ,isnull(SUM(Y.Chik),0)'Chik',isnull(SUM(Y.Bel),0)'Bel',isnull(SUM(Y.Hos),0)'Hos',ISNULL(sum(Y.Idu),0)'Idu', ISNULL(sum(Y.Nash),0)'Nash',ISNULL(sum(Y.Akol),0)'Akol',ISNULL(sum(Y.Hdd),0)'Hdd',ISNULL(sum(Y.Ban),0)'Ban', ISNULL(sum(Y.Kar),0)'Kar',ISNULL(sum(Y.RD),0)'RD',ISNULL(sum(Y.Pun),0)'Pun' from (select T1.Account,T1.Debit, T2.AcctName,T0.RefDate,T0.StornoToTr,T0.Number, (select sum(isnull(Debit,0)) from JDT1 a where a.TransId = T1.transid and a.Location = '1' and a.Account = T2.AcctCode) as 'Pat', (select sum(isnull(Debit,0)) from JDT1 a where a.TransId = T1.transid and a.Location = '2' and a.Account = T2.AcctCode) as 'Kor', (select sum(isnull(Debit,0)) from JDT1 a where a.TransId = T1.transid and a.Location = '3' and a.Account = T2.AcctCode) as 'Kur', (select sum(isnull(Debit,0)) from JDT1 a where a.TransId = T1.transid and a.Location = '4' and a.Account = T2.AcctCode) as 'Chik', (select sum(isnull(Debit,0)) from JDT1 a where a.TransId = T1.transid and a.Location = '5' and a.Account = T2.AcctCode) as 'Bel', (select sum(isnull(Debit,0)) from JDT1 a where a.TransId = T1.transid and a.Location = '6' and a.Account = T2.AcctCode) as 'Hos', (select sum(isnull(Debit,0)) from JDT1 a where a.TransId = T1.transid and a.Location = '7' and a.Account = T2.AcctCode) as 'Idu', (select sum(isnull(Debit,0)) from JDT1 a where a.TransId = T1.transid and a.Location = '8' and a.Account = T2.AcctCode) as 'Nash', (select sum(isnull(Debit,0)) from JDT1 a where a.TransId = T1.transid and a.Location = '9' and a.Account = T2.AcctCode) as 'Akol', (select sum(isnull(Debit,0)) from JDT1 a where a.TransId = T1.transid and a.Location = '10' and a.Account = T2.AcctCode) as 'Hdd', (select sum(isnull(Debit,0)) from JDT1 a where a.TransId = T1.transid and a.Location = '11' and a.Account = T2.AcctCode) as 'Ban', (select sum(isnull(Debit,0)) from JDT1 a where a.TransId = T1.transid and a.Location = '12' and a.Account = T2.AcctCode) as 'Kar', (select sum(isnull(Debit,0)) from JDT1 a where a.TransId = T1.transid and a.Location = '13' and a.Account = T2.AcctCode) as 'RD', (select sum(isnull(Debit,0)) from JDT1 a where a.TransId = T1.transid and a.Location = '14' and a.Account = T2.AcctCode) as 'Pun' from OJDT T0 Inner Join JDT1 T1 On T1.TransId = T0.TransId Left Join OACT T2 on T2.AcctCode = T1.Account)Y where y.RefDate between @FromDate and @ToDate GROUP BY Y.Account,Y.AcctName,Y.Number
Regards,
Karthik B
Dear Karthik,
Please use below query and let me know, if this suffice your requirement or not.
/*Select Y.Refdate from ojdt y*/ Declare @FromDate as Date Set @FromDate = '20161001' /*Select Y.Refdate from ojdt y*/ Declare @ToDate as Date Set @ToDate = '20161010' select Y.Number,Y.Account,Y.AcctName,isnull(SUM(Y.Pat),0) 'Pat',isnull(SUM(Y.Kor),0)'Kor' ,isnull(SUM(Y.Kur),0)'Kur' ,isnull(SUM(Y.Chik),0)'Chik',isnull(SUM(Y.Bel),0)'Bel',isnull(SUM(Y.Hos),0)'Hos',ISNULL(sum(Y.Idu),0)'Idu', ISNULL(sum(Y.Nash),0)'Nash' ,ISNULL(sum(Y.Akol),0)'Akol',ISNULL(sum(Y.Hdd),0)'Hdd',ISNULL(sum(Y.Ban),0)'Ban', ISNULL(sum(Y.Kar),0)'Kar',ISNULL(sum(Y.RD),0)'RD' ,ISNULL(sum(Y.Pun),0)'Pun' from (select T1.Account,T1.Debit, T2.AcctName,T0.RefDate,T0.StornoToTr,T0.Number ,(select sum(isnull(Debit,0)) from JDT1 a where a.TransId = T1.transid and a.Location = '1' and a.Account = T2.AcctCode) as 'Pat' ,(select sum(isnull(Debit,0)) from JDT1 a where a.TransId = T1.transid and a.Location = '2' and a.Account = T2.AcctCode) as 'Kor' ,(select sum(isnull(Debit,0)) from JDT1 a where a.TransId = T1.transid and a.Location = '3' and a.Account = T2.AcctCode) as 'Kur' ,(select sum(isnull(Debit,0)) from JDT1 a where a.TransId = T1.transid and a.Location = '4' and a.Account = T2.AcctCode) as 'Chik' ,(select sum(isnull(Debit,0)) from JDT1 a where a.TransId = T1.transid and a.Location = '5' and a.Account = T2.AcctCode) as 'Bel' ,(select sum(isnull(Debit,0)) from JDT1 a where a.TransId = T1.transid and a.Location = '6' and a.Account = T2.AcctCode) as 'Hos' ,(select sum(isnull(Debit,0)) from JDT1 a where a.TransId = T1.transid and a.Location = '7' and a.Account = T2.AcctCode) as 'Idu' ,(select sum(isnull(Debit,0)) from JDT1 a where a.TransId = T1.transid and a.Location = '8' and a.Account = T2.AcctCode) as 'Nash' ,(select sum(isnull(Debit,0)) from JDT1 a where a.TransId = T1.transid and a.Location = '9' and a.Account = T2.AcctCode) as 'Akol' ,(select sum(isnull(Debit,0)) from JDT1 a where a.TransId = T1.transid and a.Location = '10' and a.Account = T2.AcctCode) as 'Hdd' ,(select sum(isnull(Debit,0)) from JDT1 a where a.TransId = T1.transid and a.Location = '11' and a.Account = T2.AcctCode) as 'Ban' ,(select sum(isnull(Debit,0)) from JDT1 a where a.TransId = T1.transid and a.Location = '12' and a.Account = T2.AcctCode) as 'Kar' ,(select sum(isnull(Debit,0)) from JDT1 a where a.TransId = T1.transid and a.Location = '13' and a.Account = T2.AcctCode) as 'RD' ,(select sum(isnull(Debit,0)) from JDT1 a where a.TransId = T1.transid and a.Location = '14' and a.Account = T2.AcctCode) as 'Pun' from OJDT T0 Inner Join JDT1 T1 On T1.TransId = T0.TransId AND (T0.StornoToTr IS NULL OR T0.AutoStorno = 'Y') Left Join OACT T2 on T2.AcctCode = T1.Account)Y where (y.RefDate between @FromDate and @ToDate) GROUP BY Y.Account,Y.AcctName,Y.Number
Best Regards,
Rajendra. N