Hello
I need help to finish my query for aging report as 06/30/2022. My code work with the getdate() I need to replace the get date for 06/30/2022 so I can have the aging from that day to everything sitting on the system
It is a simple table
Select customer_id, trans_date, trans_amount, billing_period
, sum( case whn datediff(day,trans_dte, getdate()) between 0 and 30 then [amt] else 0 end) as [30_days]
group by billing_period, customer_id
getdate()
becomes
cast('06/30/2022') as date
Hello harishgg1
See attached I used the 06/30/22 but no data retrieved, since I need all transaction less than <= 06/30/22
Where the cast goes... I don't understand. Thank you!
no need for cast
set @date = '06302022'
should be set
@date = '06/30/2022'
declare @date datetime ;
set @date = '06/30/2022'
Hi
Thank you it work, but I have now I different issue since this is an Agin I have to add the debits and the credit...and the do the aging date. Any suggestions Thank you so much!
that is not understandable
it looks easy to do
but explain table , column values etc
Hello
Thank you so much for your help, the transaction table have all transaction since 2018 so I need an Aging report from 06/30/2022 to the first transaction of 2018,
so the report will be something like that, the bucket are different to 30 to 60 because Auditor need to see for how long those customer owe us money, ...I dont know why my number do not match 
.
0-182, 183-260, 261+
0 1500 3000
Does this do something close to what you're looking for?
DECLARE @AsOfDate date = '2022-06-30';
SELECT customer_id
, trans_date
, trans_amount
, billing_period
, DATEDIFF(day, trans_date, @AsOfDate) AS Age
, CASE
WHEN DATEDIFF(day, trans_date, @AsOfDate) < 30 THEN '0 - 29'
WHEN DATEDIFF(day, trans_date, @AsOfDate) < 60 THEN '30 - 59'
WHEN DATEDIFF(day, trans_date, @AsOfDate) < 90 THEN '60 - 89'
ELSE '90+'
END AS AgeGroup
WHERE trans_date < @AsOfDate
GROUP BY billing_period
, customer_id;
Hello SqlHippo
Im missing the total, that is my issue where I can the sum (trans_amount) and datediff
Thank you!
Sorry, I meant to REMOVE the GROUP BY.
Start with making sure the result of the DATEDIFFs and the CASE expressions are coming out correctly.
Once you've verified they're producing the correct results, use that query in either a CTE or subquery and use CASE or IIF inside COUNT for each age group.
EDIT: I just realized after looking again that you're SUMming Amt, not counting the number of transactions... I added that.
DECLARE @AsOfDate date = '2022-06-30';
WITH TransactionAges AS
(
SELECT customer_id
, trans_date
, trans_amount
, Amt
, billing_period
, DATEDIFF(day, trans_date, @AsOfDate) AS Age
FROM YourTable
WHERE trans_date < @AsOfDate
)
SELECT customer_id
, trans_date
, trans_amount
, billing_period
, COUNT(IIF( Age < 30, 1, NULL)) AS [Count 0-29]
, COUNT(IIF(Age >= 30 AND Age < 60, 1, NULL)) AS [Count 30-59]
, COUNT(IIF(Age >= 60 AND Age < 90, 1, NULL)) AS [Count 60-89]
, COUNT(IIF(Age >= 90 , 1, NULL)) AS [Count 90+]
, SUM(IIF( Age < 30, Amt, 0)) AS [Amt 0-29]
, SUM(IIF(Age >= 30 AND Age < 60, Amt, 0)) AS [Amt 30-59]
, SUM(IIF(Age >= 60 AND Age < 90, Amt, 0)) AS [Amt 60-89]
, SUM(IIF(Age >= 90 , Amt, 0)) AS [Amt 90+]
FROM TransactionAges
GROUP BY customer_id
, trans_date
, trans_amount
, billing_period
;
Hello
Thank you,
SUM(IIF( Age < 30, Amt, 0)) AS [Amt 0-29]
, SUM(IIF(Age >= 30 AND Age < 60, Amt, 0)) AS [Amt 30-59]
, SUM(IIF(Age >= 60 AND Age < 90, Amt, 0)) AS [Amt 60-89]
, SUM(IIF(Age >= 90 , Amt, 0)) AS [Amt 90+]
this part is not working, I think we should have a debit and credit and work with the total,
Hi,
it might be helpful to reduce hardcoding and add relations.
So, create a table with aging buckets
Columns: FromAge(int) , ToAge(int) , AgeBucketID (int)
values:
0 29
30 59
etc
90 9999999 ... representing "90+"
you then can join your caluclated "Age" : join ... on Age between FromAge and ToAge
and you can add the AgeBucketID to the group by instead of doing count(IIF... and SUM (IIF ....
that
... that method makes for simple SQLs and gives you the option to even have your key users maintain their analytic dimensions .. like the AgeBuckets .. by themselves thru any kind of super-simple user interface .. and be it a well-known desktop-db ....
Hi Tom
I will try it, and let you know.