?
Solved

Stored Proc to perform aging

Posted on 2002-06-24
5
Medium Priority
?
351 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 69

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 69

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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

771 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