T-SQL for deletions based on tiered datetime

Question

I have a table that essentially stores revisions of certain datasets (i.e. edits back through time if you like). The date of the revision is unimaginatively stored as a datetime field named revision.

Each new revision is taken daily, at midnight.

If this table were left to populate, there would be a set of rows for every single day, where each set of rows shares the same revision datetime field, e.g. 2010-10-31 00:00:00.

I would like to implement a stored procedure as a job that essentially runs daily and cleans up the number of revisions in this table, based on criteria similar to the way the Time Machine features works in OS X. Namely, the revisions to be kept should be:

• one revision each day in the past week (since weekly revision below)
• one revision each week for every week after this (the midnight Monday morning revision)
• one revision each month for revisions beyond a year old (the midnight revision of the first day of the month)

So, for example, right now I would expect to see the following revisions:

• 2010-10-30 (daily)
• 2010-10-29 (daily)
• 2010-10-28 (daily)
• 2010-10-27 (daily)
• 2010-10-26 (daily)
• 2010-10-25 (daily/weekly)
• 2010-10-18 (weekly)
• 2010-10-11 (weekly)
• 2010-10-04 (weekly)
• 2010-09-27 (weekly)
• ...

That is of course bearing in mind my data set isn't yet a year old.

So, what's the best and most concise DELETE FROM to achieve this? Thanks!

Answer 1

Taking each case

• Last weeek: simply filter < DATEADD(day, -8, GETDATE()) because no processing is needed. ( allows for time component and assumes clean up runs after midnight)

• Between week ago and year ago: Use DATEPART(weekday, revision) <> 2. Depends on @@datefirst.

• More than a year ago: Use DATEPART(day, revision) <> 1

So, something like but untested...

DELETE
   MyTable
WHERE
   Revision < DATEADD(day, -8, GETDATE())
   AND
   (
   Revision > DATEADD(year, -1, GETDATE()) AND DATEPART(weekday, revision) <> 2
   OR --Must be > 1 year ago)
   Revision < DATEPART(month, revision) <> 1)
    )