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.
www.cops2005.com

Programmer & Developer
.Net, WSS 3.0, SQL DBA
Check out this awsome .Net Resource! http://www.learnvisualstudio.net

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



 

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.
www.cops2005.com

Programmer & Developer
.Net, WSS 3.0, SQL DBA
Check out this awsome .Net Resource! http://www.learnvisualstudio.net

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.