Query Issue group by opening balance

[former_member252592]

Hi Experts I wright the following Query

Declare @StartDate Date = '01/01/2016' Declare @EndDate Date = '01/31/2016' SELECT (c0.Cardcode + ' - ' + c0.CardName) as 'Customer', T1.[PymntGroup] as 'Payment Terms', T2.[SlpName] as 'Sale Person', (select isnull(Sum(A.Debit-A.Credit),0) from JDT1 A inner join OJDT B on A.TransId = B.TransId where A.ShortName = C0.CardCode and B.TaxDate < @StartDate ) 'Opening Balance', CASE when J0.DocSeries = '60' then SUM (j1.Debit) when J0.DocSeries = '61' then SUM (j1.Debit) else '0' end 'Invoice', CASE J0.DocSeries when '18' then SUM (j1.debit) else '0' end 'Check Return', CASE J0.DocSeries when '15' then SUM (j1.Debit) else '0' end 'Incoming Reverse', CASE J0.DocSeries when '15' then SUM (j1.Credit) else '0' end 'Incoming Payments', CASE when J0.DocSeries = '62' then SUM (j1.Credit) when J0.DocSeries = '63' then SUM (j1.Credit) else '0' end 'CR Note', CASE when J0.DocSeries = '17' then SUM (j1.Credit) when J0.DocSeries = '18' then SUM (j1.Credit) else '0' end 'JE CR', CASE when J0.DocSeries = '17' then SUM (j1.Debit) else '0' end 'JE DR' --Sum(A.Debit - A.Credit) as 'Opening Balance' FROM OJDT J0 INNER JOIN JDT1 J1 ON J0.TransId=J1.TransId INNER JOIN OCRD C0 ON J1.ShortName=C0.CardCode inner join OCTG T1 on C0.GroupNum = T1.GroupNum INNER JOIN OSLP T2 ON T2.SlpCode = C0.SlpCode --inner join JDT1 A on A.ShortName = C0.CardCode --inner join OJDT B on A.TransId = B.TransId where J0.TaxDate between @StartDate and @EndDate and c0.CardCode like 'C%%' and T2. SlpName != '-No Sales Employee-' group by c0.CardName, c0.CardCode, T1.[PymntGroup], T2.[SlpName],J0.DocSeries Order by T2.SlpName

The result is the following

in the above pic the opeining balance is repeating on each row i need to group it by Opening balance too so the result shows in a single row of each customer

is it possible Kindly guide me

Regards,

Jamil

[Johan_H · Accepted solution]

Hi Jamil,

The doubles are caused by one or more of the case statements.

Comment them out, and you do not get doubles. You are going to have to figure out an alternative way to get that data. Perhaps similar embedded queries like you used for the opening balance?

Regards,

Johan

[former_member252592]

Thank u Johan for ur reply

I got your point and change my query to following and achieve my desired result thanx again

Declare @StartDate Date = '01/01/2016' Declare @EndDate Date = '01/31/2016' SELECT(C0.Cardcode + ' - ' + C0.CardName) as 'Customer', T1.[PymntGroup] as 'Payment Terms', T2.[SlpName] as 'Sale Person', (select isnull(Sum(A.Debit-A.Credit),0)from JDT1 A inner join OJDT B on A.TransId = B.TransId where A.ShortName = C0.CardCode and B.TaxDate < @StartDate ) 'Opening Balance', (select isnull(Sum(A.Debit),0)from JDT1 A inner join OJDT B on A.TransId = B.TransId where A.ShortName = C0.CardCode and B.TaxDate between @StartDate and @EndDate and B.DocSeries in('60','61') ) 'Invoice', (select isnull(Sum(A.Debit),0)from JDT1 A inner join OJDT B on A.TransId = B.TransId where A.ShortName = C0.CardCode and B.TaxDate between @StartDate and @EndDate and B.DocSeries in('18') ) 'Check Return', (select isnull(Sum(A.Debit),0)from JDT1 A inner join OJDT B on A.TransId = B.TransId where A.ShortName = C0.CardCode and B.TaxDate between @StartDate and @EndDate and B.DocSeries in('15') ) 'Incoming Reverse', (select isnull(Sum(A.Credit),0)from JDT1 A inner join OJDT B on A.TransId = B.TransId where A.ShortName = C0.CardCode and B.TaxDate between @StartDate and @EndDate and B.DocSeries in('15') ) 'Incoming Payments', (select isnull(Sum(A.Credit),0)from JDT1 A inner join OJDT B on A.TransId = B.TransId where A.ShortName = C0.CardCode and B.TaxDate between @StartDate and @EndDate and B.DocSeries in('62','63') ) 'CR Note', (select isnull(Sum(A.Credit),0)from JDT1 A inner join OJDT B on A.TransId = B.TransId where A.ShortName = C0.CardCode and B.TaxDate between @StartDate and @EndDate and B.DocSeries in('17') ) 'JE CR', (select isnull(Sum(A.Debit),0)from JDT1 A inner join OJDT B on A.TransId = B.TransId where A.ShortName = C0.CardCode and B.TaxDate between @StartDate and @EndDate and B.DocSeries in('15') ) 'JE DR' FROM OJDT J0 INNER JOIN JDT1 J1 ON J0.TransId=J1.TransId INNER JOIN OCRD C0 ON J1.ShortName=C0.CardCode inner join OCTG T1 on C0.GroupNum = T1.GroupNum INNER JOIN OSLP T2 ON T2.SlpCode = C0.SlpCode where J0.TaxDate between @StartDate and @EndDate and c0.CardCode like 'C%%' and T2. SlpName != '-No Sales Employee-' --and c0.cardcode = 'c0084'

Regards,

Jamil

[Johan_H]

Thanks Jamil, for sharing your solution.

[kothandaraman_nagarajan]

Hi,

Please try this,

Declare @StartDate Date = '01/01/2008' Declare @EndDate Date = '01/31/2008' SELECT distinct (C0.Cardcode + ' - ' + C0.CardName) as 'Customer', T1.[PymntGroup] as 'Payment Terms', T2.[SlpName] as 'Sale Person', (select isnull(Sum(A.Debit-A.Credit),0) from JDT1 A inner join OJDT B on A.TransId = B.TransId where A.ShortName = C0.CardCode and B.TaxDate < @StartDate ) 'Opening Balance', CASE when J0.DocSeries = '60' then SUM (j1.Debit) when J0.DocSeries = '61' then SUM (j1.Debit) else '0' end 'Invoice', CASE J0.DocSeries when '18' then SUM (j1.debit) else '0' end 'Check Return', CASE J0.DocSeries when '15' then SUM (j1.Debit) else '0' end 'Incoming Reverse', CASE J0.DocSeries when '15' then SUM (j1.Credit) else '0' end 'Incoming Payments', CASE when J0.DocSeries = '62' then SUM (j1.Credit) when J0.DocSeries = '63' then SUM (j1.Credit) else '0' end 'CR Note', CASE when J0.DocSeries = '17' then SUM (j1.Credit) when J0.DocSeries = '18' then SUM (j1.Credit) else '0' end 'JE CR', CASE when J0.DocSeries = '17' then SUM (j1.Debit) else '0' end 'JE DR' --Sum(A.Debit - A.Credit) as 'Opening Balance' FROM OJDT J0 INNER JOIN JDT1 J1 ON J0.TransId=J1.TransId INNER JOIN OCRD C0 ON J1.ShortName=C0.CardCode inner join OCTG T1 on C0.GroupNum = T1.GroupNum INNER JOIN OSLP T2 ON T2.SlpCode = C0.SlpCode --inner join JDT1 A on A.ShortName = C0.CardCode --inner join OJDT B on A.TransId = B.TransId where J0.TaxDate between @StartDate and @EndDate and c0.CardCode like 'C%%' and T2. SlpName != '-No Sales Employee-' group by c0.CardName, c0.CardCode, T1.[PymntGroup], T2.[SlpName],J0.DocSeries Order by T2.SlpName

Thanks