Calculating Aging - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Calculating Aging

Author

Topic

jrockfl
Posting Yak Master

223 Posts

Posted - 2006-05-08 : 11:46:42

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

nr
SQLTeam MVY

12543 Posts

Posted - 2006-05-08 : 11:59:25

SELECT TotalLate = sum(case when LATEST < DateAdd(Day, -1, GetDate()) then 1 else 0 end) ,
GreaterThen30Days = sum(case when LATEST < DateAdd(Day, -30, GetDate()) then 1 else 0 end) ,
x11to29 = sum(case when LATEST > DateAdd(Day, -30, GetDate())AND LATEST < DateAdd(Day, -10, GetDate()) then 1 else 0 end) ,
x6to10 = sum(case when LATEST > DateAdd(Day, -11, GetDate()) AND LATEST < DateAdd(Day, -6, GetDate()) then 1 else 0 end) ,
x1to5 = sum(case when LATEST > DateAdd(Day, -6, GetDate()) AND LATEST < DateAdd(Day, -1, GetDate()) then 1 else 0 end)
from CS_OnOrderReport
WHERE BUYER = 'JC'

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jrockfl
Posting Yak Master

223 Posts

Posted - 2006-05-08 : 12:06:03

I like the way you did it, looks a lot cleaner. I still get the same results though, is my logic correct for calculating the aging?

nr
SQLTeam MVY

12543 Posts

Posted - 2006-05-08 : 12:07:09

Probably - I haven't changed the logic just the method of execution.
The way you do it will run several scans of the table rather than just one though. Not a problem if the table is small.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources