Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Stored Proc to perform aging

Posted on 2002-06-24
5
Medium Priority
?
353 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 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

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…
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 …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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.

610 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