SQL Server find the last week of the last 2 months

Question

INSERT INTO @blah (ID, Date)
    VALUES (123, '11/12/2012')
    VALUES (124, '11/30/2012')
    VALUES (125, '11/28/2012')
    VALUES (126, '12/1/2012')
    VALUES (127, '12/30/2012')
    VALUES (128, '12/25/2012')
    VALUES (129, '12/26/2012')

I want to get rows where the date is the last week of the respective month going back two months. This month is Jan 2013, so i want the last week of Dec 2012 and Nov 2012.

The ultimate option would be the last full week of a month example: dec 2012 = 12/23-12/29 but for now ill take the last 7 days of the month.

I know how to get the last two months but unsure how to get the last week of the respective month..

select
    *
from
    @blah
where
    dateDiff(month,date,getdate()) < 2 ---only look at the last two months

Answer 1

This meets the stated requirement (last full week of previous two months):

SET DATEFIRST 1;

DECLARE @s DATE = GETDATE(), @s1 DATE, @s2 DATE;
SET @s = GETDATE();

-- last day of last month:
SET @s1 = DATEADD(DAY, -DAY(@s), @s);

-- last day of previous month:
SET @s = DATEADD(MONTH, -1, @s);
SET @s2 = DATEADD(DAY, -DAY(@s), @s);

SELECT 
 @s1 = DATEADD(DAY, -7, DATEADD(DAY, -DATEPART(WEEKDAY, @s1) % 7, @s1)),
 @s2 = DATEADD(DAY, -7, DATEADD(DAY, -DATEPART(WEEKDAY, @s2) % 7, @s2));

SELECT col1, col2, etc. 
FROM dbo.table
  WHERE 
  (date_column >= @s1 AND date_column < DATEADD(DAY, 7, @s1)
  OR
  (date_column >= @s2 AND date_column < DATEADD(DAY, 7, @s2);

To make this more dynamic (you should do your best to state these requirements FIRST, not after people have put in a bunch of work), you can say:

DECLARE @NumberOfMonthsIReallyWanted INT = 3;

DECLARE @i INT = 1, @d DATE = GETDATE();
DECLARE @t TABLE(d DATE);

WHILE @i <= @NumberOfMonthsIReallyWanted
BEGIN
  SET @d = DATEADD(MONTH, -@i, @d)

  INSERT @t(s) SELECT DATEADD(DAY, -7, DATEADD(DAY,  
   -DATEPART(WEEKDAY, DATEADD(DAY, -DAY(@d), @d)) % 7, 
   DATEADD(DAY, -DAY(@d), @d)));

  SET @i += 1;
END

SELECT src.col1, src.col2, etc. 
  FROM dbo.table AS src
  INNER JOIN @t AS t
  ON src.date_column >= t.d AND src.date_column < DATEADD(DAY, 7, t.d);

Please don't let anyone convince you to use LIKE for date comparison queries. Not only does this kill sargability (meaning no index can be used), but, for a problem like this, how do you determine what string patterns to match? The difficulty is not in constructing the WHERE clause, but rather what to fill in for the magic (Your Dates go here) placeholder. And when you do find the range of dates, do you really want 14 individual LIKE expressions? I wouldn't.