Forum Discussion
SqlProg
Apr 04, 2024Copper Contributor
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
- olafhelperBronze ContributorWe 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.