• Status: Solved
• Priority: Medium
• Security: Public
• Views: 511

# Calculating Aging Logic

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
JRockFL
• 3
1 Solution

Senior DBACommented:
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

Senior DBACommented:
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

Author Commented:
ScottPletcher,

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

0

Senior DBACommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.