Forum Discussion

SqlProg's avatar
SqlProg
Copper Contributor
Apr 04, 2024

Aging of accounts

I need to generate an aging report, and so far I have this code. All is working except the over 120. It will just add all the values in the Amount field.

 



DECLARE @DateAsOf AS Date = '12/31/2023' SELECT ClientId, SUM(case when DATEDIFF(day,convert(date,TransactionDate),convert(date,@DateAsOf)) between 1 and 31 then Amount else 0 end) AS '1-30 days', SUM(case when DATEDIFF(day,convert(date,TransactionDate),convert(date,@DateAsOf)) between 31 and 60 then Amount else 0 end) AS '31-60 days', SUM(case when DATEDIFF(day,convert(date,TransactionDate),convert(date,@DateAsOf)) between 61 and 90 then Amount else 0 end) AS '61-90 days', SUM(case when DATEDIFF(day,convert(date,TransactionDate),convert(date,@DateAsOf)) between 91 and 120 then Amount else 0 end) AS '91-120 days', SUM(case when DATEDIFF(day,convert(date,TransactionDate),convert(date,@DateAsOf)) > 120 then Amount else 0 end) AS 'Over 120' FROM Sales

 

 

1 Reply

  • olafhelper's avatar
    olafhelper
    Bronze Contributor
    We don't have your "sales" table to test it, so please post table design as DDL, some sample data as DML statement and the expected result.

Resources