Skip to Content
avatar image
Former Member

Query Issue group by opening balance

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


query.jpg (365.4 kB)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Best Answer
    Oct 19, 2016 at 10:46 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Oct 21, 2016 at 07:32 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Oct 19, 2016 at 01:44 PM

    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

    Add comment
    10|10000 characters needed characters exceeded