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
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Scott Pletcher
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.
ASKER
ScottPletcher,
Thank you! That works. I like your method. I will go over your query so I can learn from it.
Thank you! That works. I like your method. I will go over your query so I can learn from it.
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 :-) .