# Stored Proc to perform aging

Hi Experts,

Given a table with fields :

MYTABLE
---------

MYTABLEKEY = INT
CLIENTKEY = INT
ENTEREDDATE = DATETIME
BALANCE = MONEY

I want to create a stored procedure that will take as input the CLIENTKEY.
Using the input CLIENTKEY above, will then read entirely MYTABLE and will then calculate aging values: Current, 31-60, 61-90, 91-120 using ENTEREDDATE and BALANCE fields.

Afterwards, it will then return each of these aging values.

Can this be done,

jrmn
LVL 4
###### Who is Participating?

x

Senior DBACommented:
Oops, sorry about that.  I needed to be more careful with ranges since this wasn't all part of one CASE.  Try this:

SELECT clientkey,
SUM(CASE WHEN DATEDIFF(DAY, entereddate, GETDATE()) BETWEEN 0 AND 30
THEN balance ELSE 0) END AS 'Current',
SUM(CASE WHEN DATEDIFF(DAY, entereddate, GETDATE()) BETWEEN 31 AND 60
THEN balance ELSE 0) END AS '31-60',
SUM(CASE WHEN DATEDIFF(DAY, entereddate, GETDATE()) BETWEEN 61 AND 90
THEN balance ELSE 0) END AS '61-90',
SUM(CASE WHEN DATEDIFF(DAY, entereddate, GETDATE()) > 90
THEN balance ELSE 0) END AS '91-120+'
FROM myTable
WHERE clientkey = @clientkey  --optional; @clientkey is clientkey passed to the stored procedure (SP)
GROUP BY clientkey

0

Commented:
Will definitely possible.

Could you confirm the mathematical formulae for the aging values please?
Cheers,
0

Author Commented:
Hi lozzamoore,

If the current date is 12/30/98 :

Aged 0 To 30 Days (or Current) specifies the period from 12/1/98 to the present date.

Aged 31 To 60 Days specifies the period from 11/1/98 to 11/30/98.

Aged 61 To 90 Days specifies the period 10/2/98 to 10/31/98.

Aged 91 to 120 Days specifies the period 09/02/98 to 10/1/98

Is this information enough?

Thanks for the help,
jrmn
0

Senior DBACommented:
This should get you close:

SELECT clientkey,
SUM(CASE WHEN DATEDIFF(DAY, entereddate, GETDATE()) <= 30
THEN balance ELSE 0) END AS 'Current',
SUM(CASE WHEN DATEDIFF(DAY, entereddate, GETDATE()) <= 60
THEN balance ELSE 0) END AS '31-60',
SUM(CASE WHEN DATEDIFF(DAY, entereddate, GETDATE()) <= 90
THEN balance ELSE 0) END AS '61-90',
SUM(CASE WHEN DATEDIFF(DAY, entereddate, GETDATE()) > 90
THEN balance ELSE 0) END AS '91-120+'
FROM myTable
WHERE clientkey = @clientkey  --optional; @clientkey is clientkey passed to the stored procedure (SP)
GROUP BY clientkey

This will calculate all balances in one statement.  If a SP, those results will be passed back to the caller.  Or you could create a temp table to hold the data (be sure to create the temp table in the calling SP so that it won't accidentally drop away when the called SP ends).
0

Author Commented:
Hi ScottPletcher,

Anyway, I have set up on my DB a BALANCE (\$40.00 to be exact) for a certain CLIENT that will fall under the aging 31-60. When I run the SQL code you have above, I got these results for each of the columns:

Current   31-60  61-90  91-120+
0         40     40     0

The column that will return a value should only be column 31-60 and the rest should return zero(0) as the BALANCE I set up above falls on this aging range.

I think there's a little problem on the your query.

Is it possible for you to modify the query you gave?

Thanks,
jrmn
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.