Solved

Stored Proc to perform aging

Posted on 2002-06-24
5
328 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 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 75 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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

832 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