Solved

# Calculating Aging Logic

Posted on 2006-05-08
Medium Priority
507 Views
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
AND LATEST < DateAdd(Day, -1, GetDate())
) a,
(
SELECT COUNT(PO) AS GreaterThen30Days
FROM CS_OnOrderReport
AND LATEST < DateAdd(Day, -30, GetDate())
) b,
(
SELECT COUNT(PO) AS x11to29
FROM CS_OnOrderReport
AND LATEST > DateAdd(Day, -30, GetDate())
AND LATEST < DateAdd(Day, -10, GetDate())
) c,
(
SELECT COUNT(PO) AS x6to10
FROM CS_OnOrderReport
AND LATEST > DateAdd(Day, -11, GetDate())
AND LATEST < DateAdd(Day, -6, GetDate())
) d,
(
SELECT COUNT(PO) AS x1to5
FROM CS_OnOrderReport
AND LATEST > DateAdd(Day, -6, GetDate())
AND LATEST < DateAdd(Day, -1, GetDate())
)e
0
Question by:JRockFL
• 3

LVL 70

Accepted Solution

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
0

LVL 70

Expert Comment

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

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

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

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
Course of the Month17 days, 2 hours left to enroll