Post reply

Using CASE in a delete statement

  • October 27, 2008 at 1:50 pm

    #72152

    The goal is to keep a rolling 13 months of data within a table. The following query is what I am using:

    delete from table where year(asofdate)=year(getdate())-1

    and month(asofdate) = Month(getdate())-2

    Now, I need to modify this to adjust for when the month is Jan or Feb. In these cases I want to subtract 2 from the year. I know this is not correct, but it was the clearest way I could explain what I'm looking for.

    delete from table where

    case when (DATEPART(month, GETDATE())= 1)

    then year(asofdate)=year(getdate())-2 and month(asofdate) = Month(getdate())-2

    case when(DATEPART(month, GETDATE())= 2)

    then year(asofdate)=year(getdate())-2 and month(asofdate) = Month(getdate())-2

    else

    year(asofdate)=year(getdate())-1 and month(asofdate) = Month(getdate())-2

    Thanks for any suggestions.

  • October 27, 2008 at 2:11 pm

    #890239

    Molly, maybe this would be easier...

    WHERE DATEDIFF(m,asofdate,GETDATE()) < = 13

    Greg
    _________________________________________________________________________________________________
    The glass is at one half capacity: nothing more, nothing less.

  • October 27, 2008 at 2:35 pm

    #890254

    When you are talking about keeping a rolling 13 months worth of data, are you talking about current month plus the last 12 previous months? For example, if you were to run the delete today, you'd keep October 2008 back through October 2007?

    😎

  • October 27, 2008 at 2:42 pm

    #890259

    Here is the code I'd try:

    delete from table

    where

    asofdate < dateadd(mm, -13, dateadd(mm, datediff(mm, 0, getdate()), 0))

    😎

  • October 27, 2008 at 2:44 pm

    #890262

    Lynn,

    Yes, that is what I am trying to do.

    Thanks

  • October 27, 2008 at 2:51 pm

    #890266

    You may have to adjust the code I posted above to subtract 12 instead of 13. Give that code a test in a test environment. better yet, change it to a select and see if it returns the rows you want to delete.

    😎

  • November 6, 2008 at 9:00 am

    #894970

    Thanks for your replies! Lynn, your query worked great.

  • November 6, 2008 at 9:08 am

    #894978

    Glad to have been able to help.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply