SQLTeam.com | Weblogs | Forums

Help with query for Aging report

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

image

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

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.