Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Stored Proc to perform aging

Posted on 2002-06-24
5
Medium Priority
?
357 Views
Last Modified: 2010-05-18
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,

Please Help,
jrmn
0
Comment
Question by:jrmn
  • 2
  • 2
5 Comments
 
LVL 7

Expert Comment

by:lozzamoore
ID: 7106835
Will definitely possible.

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

Author Comment

by:jrmn
ID: 7106883
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
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 7108224
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
 
LVL 4

Author Comment

by:jrmn
ID: 7109700
Hi ScottPletcher,

Thanks for the reply.
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
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 300 total points
ID: 7110507
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

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Suggested Courses

572 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question