Aging of accounts

Copper Contributor

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
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.