aging report 30,60,90, 1Yr and each year after.

[Glowworm27]

All I need to display a group of records for the week. This group of records will be all records that are either 30, 60, 90 or 1yr old and each yr after. Because this is a weekly report I will be displaying records 30-37 days old, 60-67 days, 90-97 days and 365 to 372 days old and every year after that.

Here is the issue.

How can I write a query to give me records that are 1yr, 2yr, 3 yr, and each year after into this report.

I can manually code this for 10 years, but going forward I will need to alter this code every year and add an addition where clause.

Here is the query so far....

SELECT *
FROM [WAMAIN]
where DISPCODE = 'ACTW'
And ((ADDTIME between DATEADD(dd,-37, getdate()) and DATEADD(dd,-30, getdate())) -- 30 days
OR (ADDTIME between DATEADD(dd,-67, getdate()) and DATEADD(dd,-60, getdate())) -- 60 days
OR (ADDTIME between DATEADD(dd,-97, getdate()) and DATEADD(dd,-90, getdate())) -- 90 days
OR (ADDTIME between DATEADD(dd,-372, getdate()) and DATEADD(dd,-365, getdate())) -- 1 yr
-- need to figure out older yearly stuff in some sort of calculated way and eliminate this hand-coded filtering
OR (ADDTIME between DATEADD(dd,-737, getdate()) and DATEADD(dd,-730, getdate())) -- 2 yr
OR (ADDTIME between DATEADD(dd,-1102, getdate()) and DATEADD(dd,-1095, getdate())) -- 3 yr
OR (ADDTIME between DATEADD(dd,-1467, getdate()) and DATEADD(dd,-1460, getdate())) -- 4 yr
OR (ADDTIME between DATEADD(dd,-1932, getdate()) and DATEADD(dd,-1825, getdate())) -- 5 yr
OR (ADDTIME between DATEADD(dd,-2197, getdate()) and DATEADD(dd,-2190, getdate())) -- 6 yr
OR (ADDTIME between DATEADD(dd,-2562, getdate()) and DATEADD(dd,-2555, getdate())) -- 7 yr
OR (ADDTIME between DATEADD(dd,-2927, getdate()) and DATEADD(dd,-2920, getdate())) -- 8 yr
OR (ADDTIME between DATEADD(dd,-3292, getdate()) and DATEADD(dd,-3285, getdate())) -- 9 yr
OR (ADDTIME between DATEADD(dd,-3657, getdate()) and DATEADD(dd,-3650, getdate())) -- 10 yr
OR (ADDTIME between DATEADD(dd,-4022, getdate()) and DATEADD(dd,-4015, getdate())) -- 11 yr
)
Order by ADDTIME

George Oakes
CEO & President
COPS Software, Inc.

http://www.cops2005.com

Programmer & Developer
.Net, WSS 3.0, SQL DBA
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net

 

[PWise]

Not sure how you are accounting for leap years

but for yor date i would create a tabel

ageingDatesAdd

StartDate int
Enddate int

Startdate enddate
-37 -30
-67 -60
-97 -90
-372 -362
-737 -730
...
-4022 -4015

SELECT *
FROM [WAMAIN]
inner join ageingDatesAdd
on ADDTIME between DATEADD(dd,Startdate , getdate()) and DATEADD(dd,enddate, getdate())
And DISPCODE = 'ACTW'
Order by ADDTIME

now all you have to do is add more years to the table

 

[Glowworm27]

I had thought of a table, but was hoping for a more Programatic way of doing this.

As for leap years, the DATEADD function does take that into account.


George Oakes
CEO & President
COPS Software, Inc.

http://www.cops2005.com

Programmer & Developer
.Net, WSS 3.0, SQL DBA
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net

 

[JonFer]

For your criteria to find 1+ years old records, you could compare getdate() to a calculated date using the month/day from ADDTIME and using the year from "Year(getdate())-1". That will get you any number of prior years with one check. In your Select you can use a Case statement to generate the 30/60/90 days groups with an Else clause to get the 1+ years.