Does anyone have an AR aging summary by customer but showing the aging buckets 30 60 90 etc..

[Former Member]

I am looking for a summary by customer no invoice detail, just summary by customer with aging buckets. Below is my query with invoice detail not sure how to get a summary out of this with aging buckets. Declare @d datetime set @d /*Select 1 from jdt1 t where t.duedate*/=[%1] select * from ( SELECT T1.CardCode, T1.CardName, T0.RefDate, T0.Ref1 'Document Number',     CASE  WHEN T0.TransType=13 THEN 'Invoice'           WHEN T0.TransType=14 THEN 'Credit Note'           WHEN T0.TransType=30 THEN 'Journal'           WHEN T0.TransType=24 THEN 'Receipt'           END AS 'Document Type',     T0.DueDate, (T0.Debit- T0.Credit) 'Balance'     ,ISNULL((SELECT T0.Debit-T0.Credit WHERE DateDiff(day, T0.DueDate,@d)=0 and DateDiff(day, T0.DueDate,@d)30 and DateDiff(day, T0.DueDate,@d)60 and DateDiff(day, T0.DueDate,@d)90 and DateDiff(day, T0.DueDate,@d)=121),0) '121+ Days' FROM JDT1 T0 INNER JOIN OCRD T1 ON T0.ShortName = T1.CardCode WHERE  T1.CardType = 'C' ) sub

[kothandaraman_nagarajan · Accepted solution]

Hi,

Did you checked my query?

Thanks & Regards,

Nagarajan

[Former Member · Accepted solution]

Thank you so much this is what I was looking for!!!

[Former Member · Accepted solution]

Hi,

Have you checked the result of the query?

Regards,

Amrut Sabnis

[Former Member]

Hi Danielle,

Try this query.

SELECT T1.CardCode, T1.CardName, T0.RefDate, T0.Ref1 'Document Number',

     CASE  WHEN T0.TransType=13 THEN 'Invoice'

          WHEN T0.TransType=14 THEN 'Credit Note'

          WHEN T0.TransType=30 THEN 'Journal'

          WHEN T0.TransType=24 THEN 'Receipt'

          END AS 'Document Type',

     T0.DueDate, (T0.Debit- T0.Credit) 'Balance'

     ,ISNULL((SELECT T0.Debit-T0.Credit WHERE DateDiff(day, T0.DueDate, getdate())<=-1),0) 'Future'

     ,ISNULL((SELECT T0.Debit-T0.Credit WHERE DateDiff(day, T0.DueDate,getdate())>=0 and DateDiff(day, T0.DueDate,getdate())<=30),0) 'Current'

     ,ISNULL((SELECT T0.Debit-T0.Credit WHERE DateDiff(day, T0.DueDate,getdate())>30 and DateDiff(day, T0.DueDate,getdate())<=60),0) '31-60 Days'

     ,ISNULL((SELECT T0.Debit-T0.Credit WHERE DateDiff(day, T0.DueDate,getdate())>60 and DateDiff(day, T0.DueDate,getdate())<=90),0) '61-90 Days'

     ,ISNULL((SELECT T0.Debit-T0.Credit WHERE DateDiff(day, T0.DueDate,getdate())>90 and DateDiff(day, T0.DueDate,getdate())<=120),0) '91-120 Days'

     ,ISNULL((SELECT T0.Debit-T0.Credit WHERE DateDiff(day, T0.DueDate,getdate())>=121),0) '121+ Days'

FROM JDT1 T0 INNER JOIN OCRD T1 ON T0.ShortName = T1.CardCode

WHERE  T1.CardType = 'C' and t1.cardcode = 'c0002'

Put your Cardcode instead of 'c0002'

Hope it will help you.

Regards,

Amrut Sabnis.

[Former Member]

Thank you, but I already have this. I want summary, not invoice detail.  Can I get each customer by 30 60 90 120 .. (one line) and the total per bucket. amount?

[Former Member]

Hi,

Try this. I have made changes to get sum.

I think this is exactly what you are looking for.

This query results data for all customers, if you want for particular customer,  then use where condition. Try this and let me know if this is not the exactly the same you are looking for.

SELECT CardCode, CardName, SUM(Balance) BALANCE, SUM(A) FUTURE, SUM(B) '0-30', SUM(C) '31-60', SUM(D) '61-90', SUM(E) '91-120', SUM(F) '121+' FROM (

SELECT T1.CardCode, T1.CardName, T0.RefDate, T0.Ref1 'Document_Number',

     CASE  WHEN T0.TransType=13 THEN 'Invoice'

          WHEN T0.TransType=14 THEN 'Credit Note'

          WHEN T0.TransType=30 THEN 'Journal'

          WHEN T0.TransType=24 THEN 'Receipt'

          END AS 'Document_Type',

     T0.DueDate, (T0.Debit- T0.Credit) 'Balance'

     ,ISNULL((SELECT T0.Debit-T0.Credit WHERE DateDiff(day, T0.DueDate, getdate())<=-1),0) 'A'

     ,ISNULL((SELECT T0.Debit-T0.Credit WHERE DateDiff(day, T0.DueDate,getdate())>=0 and DateDiff(day, T0.DueDate,getdate())<=30),0) 'B'

     ,ISNULL((SELECT T0.Debit-T0.Credit WHERE DateDiff(day, T0.DueDate,getdate())>30 and DateDiff(day, T0.DueDate,getdate())<=60),0) 'C'

     ,ISNULL((SELECT T0.Debit-T0.Credit WHERE DateDiff(day, T0.DueDate,getdate())>60 and DateDiff(day, T0.DueDate,getdate())<=90),0) 'D'

     ,ISNULL((SELECT T0.Debit-T0.Credit WHERE DateDiff(day, T0.DueDate,getdate())>90 and DateDiff(day, T0.DueDate,getdate())<=120),0) 'E'

     ,ISNULL((SELECT T0.Debit-T0.Credit WHERE DateDiff(day, T0.DueDate,getdate())>=121),0) 'F'

FROM JDT1 T0 INNER JOIN OCRD T1 ON T0.ShortName = T1.CardCode

WHERE  T1.CardType = 'C'

) T100

GROUP BY CARDCODE, CARDNAME

ORDER BY CARDCODE

Regards,

Amrut Sabnis.

[kothandaraman_nagarajan]

Hi,

Try this:

declare @d datetime

declare @duedate varchar(20)

declare @cardcode varchar(20)

set @duedate =1

set @cardcode =1

/*Select 1 from jdt1 t where t.duedate =[%0]*/

/*Select 1 from ocrd t1 WHERE T1.CardCode =[%2] */

select * from

(

SELECT T1.CardCode, T1.CardName, T0.RefDate, T0.Ref1 'Document Number',

     CASE  WHEN T0.TransType=13 THEN 'Invoice'

          WHEN T0.TransType=14 THEN 'Credit Note'

          WHEN T0.TransType=30 THEN 'Journal'

          WHEN T0.TransType=24 THEN 'Receipt'

          END AS 'Document Type',

     T0.DueDate, (T0.Debit- T0.Credit) 'Balance'

     ,ISNULL((SELECT T0.Debit-T0.Credit WHERE DateDiff(day, T0.DueDate,@d)<=-1),0) 'Future'

     ,ISNULL((SELECT T0.Debit-T0.Credit WHERE DateDiff(day, T0.DueDate,@d)>=0 and DateDiff(day, T0.DueDate,@d)<=30),0) 'Current'

     ,ISNULL((SELECT T0.Debit-T0.Credit WHERE DateDiff(day, T0.DueDate,@d)>30 and DateDiff(day, T0.DueDate,@d)<=60),0) '31-60 Days'

     ,ISNULL((SELECT T0.Debit-T0.Credit WHERE DateDiff(day, T0.DueDate,@d)>60 and DateDiff(day, T0.DueDate,@d)<=90),0) '61-90 Days'

     ,ISNULL((SELECT T0.Debit-T0.Credit WHERE DateDiff(day, T0.DueDate,@d)>90 and DateDiff(day, T0.DueDate,@d)<=120),0) '91-120 Days'

     ,ISNULL((SELECT T0.Debit-T0.Credit WHERE DateDiff(day, T0.DueDate,@d)>=121),0) '121+ Days'

FROM JDT1 T0 INNER JOIN OCRD T1 ON T0.ShortName = T1.CardCode

WHERE  T1.CardType = 'C' and t1.cardcode = '[%2]'

) sub

Thanks & Regards,

Nagarajan