?
Solved

Calculating Aging Logic

Posted on 2006-05-08
4
Medium Priority
?
507 Views
Last Modified: 2010-02-09
I need a second pair of eyes to look over this query. I'm trying to calculate aging. 1 to 5 days, 6 to 10 days..etc
a.TotalLate should equal the some of the others, but doesn't always.

If LATEST was to equal 5/7/2006 Then it would be late.

SELECT a.TotalLate, b.GreaterThen30Days, c.x11to29, d.x6to10, e.x1to5
FROM
(
SELECT COUNT(PO) AS TotalLate
FROM CS_OnOrderReport
WHERE BUYER = 'JC'
AND LATEST < DateAdd(Day, -1, GetDate())
) a,
(
SELECT COUNT(PO) AS GreaterThen30Days
FROM CS_OnOrderReport
WHERE BUYER = 'JC'
AND LATEST < DateAdd(Day, -30, GetDate())
) b,
(
SELECT COUNT(PO) AS x11to29
FROM CS_OnOrderReport
WHERE BUYER = 'JC'
AND LATEST > DateAdd(Day, -30, GetDate())
AND LATEST < DateAdd(Day, -10, GetDate())
) c,
(
SELECT COUNT(PO) AS x6to10
FROM CS_OnOrderReport
WHERE BUYER = 'JC'
AND LATEST > DateAdd(Day, -11, GetDate())
AND LATEST < DateAdd(Day, -6, GetDate())
) d,
(
SELECT COUNT(PO) AS x1to5
FROM CS_OnOrderReport
WHERE BUYER = 'JC'
AND LATEST > DateAdd(Day, -6, GetDate())
AND LATEST < DateAdd(Day, -1, GetDate())
)e
0
Comment
Question by:JRockFL
  • 3
4 Comments
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 16632494
All those tables will be cross-joined, so you are probably getting some very high totals.  Please try this:


SELECT SUM(CASE WHEN LATEST < DateAdd(Day, -1, GetDate())
       THEN 1 ELSE 0 END) AS [TotalLate],
   SUM(CASE WHEN LATEST < DateAdd(Day, -30, GetDate())
        THEN 1 ELSE 0 END) AS [GreaterThen30Days],
   SUM(CASE WHEN LATEST >= DateAdd(Day, -30, GetDate()) AND LATEST < DateAdd(Day, -10, GetDate())
        THEN 1 ELSE 0 END) AS [x11to29],
   SUM(CASE WHEN LATEST > DateAdd(Day, -11, GetDate()) AND LATEST < DateAdd(Day, -6, GetDate())
        THEN 1 ELSE 0 END) AS [x6to10],
   SUM(CASE WHEN LATEST > DateAdd(Day, -6, GetDate()) AND LATEST <= DateAdd(Day, -1, GetDate())
        THEN 1 ELSE 0 END) AS [x1to5]
FROM CS_OnOrderReport
WHERE BUYER = 'JC'
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 16632523
Btw, "[x11to29]" should probably be renamed "[x11to30]", otherwise you don't have any place for exactly 30 days late :-)  -- I already adjusted the comparison just forgot to adjust the name.
0
 
LVL 8

Author Comment

by:JRockFL
ID: 16632628
ScottPletcher,

Thank you! That works. I like your method. I will go over your query so I can learn from it.

0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 16633043
Glad it helped!  Btw, be sure to verify the < vs. >= (and < vs. <=) on *all* ranges.  I didn't do that in great detail, but you will need to to insure accurate results :-) .
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

864 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