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

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
0
JRockFL
Asked:
JRockFL
  • 3
1 Solution
 
Scott PletcherSenior 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
WHERE BUYER = 'JC'
0
 
Scott PletcherSenior 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
 
JRockFLAuthor Commented:
ScottPletcher,

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

0
 
Scott PletcherSenior 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.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now