Solved

Stored Proc to perform aging

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
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.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

724 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