SQL Server find the last week of the last 2 months

问题:

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

回答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.

回答2:

declare @blah table (ID int, [Date] datetime)
INSERT INTO @blah (ID, [Date])
select 123, '20121112'
union select 124, '20121130'
union select 125, '20121128'
union select 126, '20121201'
union select 127, '20121230'
union select 128, '20121225'
union select 129, '20121226'

select ID, [Date], datepart(week, [Date])
from @blah
where
    datediff(month, [Date], getdate()) in (1,2)
    and
    datepart(week, [Date]) = datepart(
                                week, 
                                dateadd(
                                    day, 
                                    -datepart(day,dateadd(month, 1, [Date])), 
                                    dateadd(month, 1, [Date])))

回答3:

This works in Oracle - may give you some ideas and hopefully helps:

-- Last weeks of last two months -- 
SELECT mydate
  , TRUNC(mydate, 'iw') wk_starts
  , TRUNC(mydate, 'iw') + 7 - 1/86400 wk_ends
  , TO_NUMBER (TO_CHAR (mydate, 'IW')) ISO_wk#  
 FROM
  ( -- Last week = Last day of the year (hardcoded) - 1 week --
   SELECT(CASE WHEN start_date = To_Date('12/31/2012', 'mm/dd/yyyy') THEN start_date-7 ELSE start_date END) mydate 
     FROM
     ( -- Last 2 months --
      SELECT Add_Months(Last_Day(Trunc(SYSDATE)), - LEVEL) start_date
        FROM dual
     CONNECT BY LEVEL <= 2
    )
 )
/

回答4:

This is somewhat dependent on your sql server. This example is similar to Oracle. Each server has its own functions, like SYSDATE and NOW.

For example.

SELECT * from blah WHERE TO_CHAR(TRUNC(data), MM/DD/YYYY) < '01/14/2013'