Group days by week

Question

Is there is a way to group dates by week of month in SQL Server?

For example

Week 2: 05/07/2012 - 05/13/2012
Week 3: 05/14/2012 - 05/20/2012

but with Sql server statement

I tried

SELECT SOMETHING, 
     datediff(wk, convert(varchar(6), getdate(), 112) + '01', getdate()) + 1 AS TIME_
FROM STATISTICS_
GROUP BY something, TIME_
ORDER BY TIME_

but it returns the week number of month. (means 3)

How to get the pair of days for current week ?

For example, now we are in third (3rd) week and I want to show 05/14/2012 - 05/20/2012

I solved somehow:

SELECT DATEADD(ww, DATEDIFF(ww,0,<my_column_name>), 0)

select DATEADD(ww, DATEDIFF(ww,0,<my_column_name>), 0)+6

Then I will get two days and I will concatenate them later.

Answer 1

All right, bear with me here. We're going to build a temporary calendar table that represents this month, including the days from before and after the month that fall into your definition of a week (Monday - Sunday). I do this in a lot of steps to try to make the process clear, but I probably haven't excelled at that in this case.

We can then generate the ranges for the different weeks, and you can join against your other tables using that.

SET DATEFIRST 7;
SET NOCOUNT ON;

DECLARE @today SMALLDATETIME, @fd SMALLDATETIME, @rc INT;

SELECT @today = DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0), -- today
    @fd = DATEADD(DAY, 1-DAY(@today), @today), -- first day of this month
    @rc = DATEPART(DAY, DATEADD(DAY, -1, DATEADD(MONTH, 1, @fd)));-- days in month

DECLARE @thismonth TABLE (
    [date]       SMALLDATETIME,
    [weekday]    TINYINT,
    [weeknumber] TINYINT 
);

;WITH n(d) AS (
    SELECT TOP (@rc+12) DATEADD(DAY, ROW_NUMBER() OVER 
    (ORDER BY [object_id]) - 7, @fd) FROM sys.all_objects
)
INSERT @thismonth([date], [weekday]) SELECT d, DATEPART(WEEKDAY, d) FROM n;

DELETE @thismonth WHERE [date] < (SELECT MIN([date]) FROM @thismonth WHERE [weekday] = 2)
                     OR [date] > (SELECT MAX([date]) FROM @thismonth WHERE [weekday] = 1);

;WITH x AS ( SELECT [date], weeknumber, rn = ((ROW_NUMBER() OVER 
   (ORDER BY [date])-1) / 7) + 1 FROM @thismonth ) UPDATE x SET weeknumber = rn;

-- now, the final query given all that (I've only broken this up to get rid of the vertical scrollbars):

;WITH ranges(w,s,e) AS (
    SELECT weeknumber, MIN([date]), MAX([date]) FROM @thismonth GROUP BY weeknumber
)
SELECT [week] = CONVERT(CHAR(10), r.s, 120) + ' - ' + CONVERT(CHAR(10), r.e, 120)

 --, SOMETHING , other columns from STATISTICS_?

 FROM ranges AS r

 -- LEFT OUTER JOIN dbo.STATISTICS_ AS s
 -- ON s.TIME_ >= r.s AND s.TIME_ < DATEADD(DAY, 1, r.e) 

 -- comment this out if you want all the weeks from this month: 
 WHERE w = (SELECT weeknumber FROM @thismonth WHERE [date] = @today)

 GROUP BY r.s, r.e --, SOMETHING
 ORDER BY [week];

Results with WHERE clause:

week
-----------------------
2012-05-14 - 2012-05-20

Results without WHERE clause:

week
-----------------------
2012-04-30 - 2012-05-06
2012-05-07 - 2012-05-13
2012-05-14 - 2012-05-20
2012-05-21 - 2012-05-27
2012-05-28 - 2012-06-03

Note that I chose YYYY-MM-DD on purpose. You should avoid regional formatting like M/D/Y especially for input but also for display. No matter how targeted you think your audience is, you're always going to have someone who thinks 05/07/2012 is July 5th, not May 7th. With YYYY-MM-DD there is no ambiguity whatsoever.

Answer 2

Create a calendar table, then you can query week numbers, first/last days of specific weeks and months etc. You can also join on it queries to get a date range etc.

Answer 3

How about a case statement?

case when datepart(day, mydatetime) between 1 and 7 then 1
     when datepart(day, mydatetime) between 8 and 14 then 2
     ...

You'll also have to include the year & month unless you want all the week 1s in the same group.

Answer 4

It's not clear of you want to "group dates by week of month", or alternately "select data from a given week"

If you mean "group" this little snippet should get you 'week of month':

SELECT   <stuff>
FROM     CP_STATISTICS
WHERE    Month(<YOUR DATE COL>) = 5 --april
GROUP BY  Year(<YOUR DATE COL>), 
          Month(<YOUR DATE COL>),
          DATEDIFF(week, DATEADD(MONTH, DATEDIFF(MONTH, 0, <YOUR DATE COL>), 0)
           , <YOUR DATE COL>) +1

Alternately, if you want "sales for week 1 of April, ordered by date" You could do something like..

DECLARE @targetDate datetime2 = '5/3/2012'
DECLARE @targetWeek int = DATEDIFF(week, DATEADD(MONTH, 
                             DATEDIFF(MONTH, 0, @targetDate), 0), @targetDate) +1


SELECT   <stuff>
FROM     CP_STATISTICS
WHERE    MONTH(@targetDate) = Month(myDateCol) AND
         YEAR(@targetDate)  = Year (myDateCol) AND 
         @targetWeek = DATEDIFF(week, DATEADD(MONTH, 
                                DATEDIFF(MONTH, 0, myDateCol), 0), myDateCol) +1
ORDER BY myDateCol

Note, things would get more complicated if you use non-standard weeks, or want to reach a few days into an earlier month for weeks that straddle a month boundary.

EDIT 2

From looking at your 'solved now' section. I think your question is "how do I get data out of a table for a given week?"

Your solution appears to be:

DECLARE @targetDate datetime2 = '5/1/2012'
DECLARE @startDate  datetime2 = DATEADD(ww, DATEDIFF(ww,0,targetDate), 0)
DECLARE @endDate    datetime2 = DATEADD(ww, DATEDIFF(ww,0,@now), 0)+6

SELECT  <stuff>
FROM    STATISTICS_
WHERE   dateStamp >= @startDate AND dateStamp <= @endDate

Notice how if the date is 5/1 this solution results in a start date of '4/30/2012'. I point this out because your solution crosses month boundaries. This may or may not be desirable.