CODEWITHSUNDEEP
sqlt-sql
dk2014
dk2014

Reputation: 19

Get current and last month data if year and month are separate columns,

I'm trying to import current and last month data into a table. Before I import the data I want to delete all the current month data as well as last month data. The problem I'm having is that the year and month are separate columns. Most of the time I could use the following statement to delete the old data before I import the new data:

DELETE FROM MyTable 
WHERE YearColumn = YEAR(GETDATE()) AND MonthColumn >= MONTH(DATEADD(MM, - 1, (GETDATE())))

However, the problem with this statement is what happens when January comes along. Say, in January next year, this statement will delete all the records with YearColumn = 2016 and MonthColumn >= 12 while I'd want to delete all the records where YearColumn = 2015 and MonthColumn >= 12 as well as YearColumn = 2016 and MonthColumn >= 1.

What would be the best way to do this? I have some ideas with case statement in the where clause but it seems it would be pretty complicated and probably slow as well.

Thanks,

Upvotes: 0

Views: 533

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You can just apply the same logic to year that you apply to month:

DELETE FROM MyTable 
WHERE YearColumn = YEAR(DATEADD(month, - 1, GETDATE())) AND
      MonthColumn >= MONTH(DATEADD(month, - 1, GETDATE()))

As a note: I much prefer spelling out the date part names rather than using abbreviations. That way, no one has to think twice about whether MM means months or minutes.

Upvotes: 1

shawnt00
shawnt00

Reputation: 17915

A case expression would be fine too. I doubt you have much to worry about.

DELETE FROM MyTable 
WHERE
             YearColumn =  YEAR(DATEADD(month, -1, GETDATE()))
        AND MonthColumn = MONTH(DATEADD(month, -1, GETDATE()))
    OR       YearColumn =  YEAR(GETDATE())
        AND MonthColumn = MONTH(GETDATE());

If performance is really an issue you could try adding an extra sargable condition:

DELETE FROM MyTable 
WHERE
    (
                 YearColumn =  YEAR(DATEADD(month, -1, GETDATE()))
            AND MonthColumn = MONTH(DATEADD(month, -1, GETDATE()))
        OR       YearColumn =  YEAR(GETDATE())
            AND MonthColumn = MONTH(GETDATE())
    )
    /* this might help with performance issues */
    AND YearColumn >= YEAR(DATEADD(month, -1, GETDATE());

Upvotes: 0

Related Questions