Grouping Date Range by Week

Question

I have a date range where a start date is 2 years prior to today. e.g.) '05/29/2007' ~ '05/29/2009'.

How can I break above date range so that I would get a list like the following?

(start date starts with "05/27/2007", not "05/29/2007" since the start of weekday is Sunday and '05/27/2007' is the first day of week for '05/29/2007' and the same reasoning for the last EndDate, 05/30/2009, which is Saturday)

StartDate EndDate
05/27/2007 06/02/2007
06/03/2007 06/09/2007
...
05/24/2009 05/30/2009

[UPDATE] here is my final query

WITH hier(num, lvl) AS (
 SELECT  0, 1
  UNION ALL
 SELECT  100, 1
  UNION ALL 
 SELECT  num + 1, lvl + 1
 FROM    hier
 WHERE   lvl < 100
)
SELECT  num, lvl,
 DATEADD(dw, -DATEPART(dw, '2007-05-29'), '2007-05-29') + num  * 7,
 DATEADD(dw, -DATEPART(dw, '2007-05-29'), '2007-05-29') + (num + 1) * 7
FROM    hier
where num <= 104 --; 52 weeks/year * 2
ORDER BY num

Answer 1

You need to make sure that @@DATEFIRST is properly sent, then you can simply use the code below. Read up on DATEFIRST though so that you understand it fully.

SET DATEFIRST 1

DECLARE @my_date DATETIME

SET @my_date = '2007-05-29'

SELECT
     DATEADD(dw, -DATEPART(dw, @my_date), @my_date) AS StartDate,
     DATEADD(dw, 6 - DATEPART(dw, @my_date), @my_date) AS EndDate

Answer 2

WITH hier(num, lvl) AS (
        SELECT  0, 1
        UNION ALL
        SELECT  100, 1
        UNION ALL 
        SELECT  num + 1, lvl + 1
        FROM    hier
        WHERE   lvl < 100
        )
SELECT  DATEADD(dw, -DATEPART(dw, '29.05.2007'), '29.05.2007') + num  * 7,
        DATEADD(dw, -DATEPART(dw, '29.05.2007'), '29.05.2007') + (num + 1) * 7
FROM    hier
WHERE   DATEADD(dw, -DATEPART(dw, '29.05.2007'), '29.05.2007') + num * 7 < '29.05.2009' 
ORDER BY
        num

This will generate a rowset with the ranges you need.

Answer 3

Should be pretty simple directy in your SQL statement... I've had a long history of dealing with dates and date arithmetic, so I would approach something like:

select 
      datepart( year, YourDateField ) as GroupYear,
      datepart( week, ( YourDateField - datepart( day, YourDateField ) +1 ) as GroupWeek,
      YourDateField,
      OtherFields
  from 
      YourTable
  where 
      whateverDateRange...
  group by 
     GroupYear,
     GroupWeek

The reason for the year and week is if you span multiple years, you would have week 1 of both years before week 2 of first year, etc..

Now, how the math works... for the GroupWeek. This will actually compute the first day of the week based on whatever date your data has as the basis... Say you have data for May 25, 26, 27 this year... They would respectively be the 2nd, 3rd and 4th day of the week -- being Sunday starts the week at day 1. So:

May 25 - 2 (day of week) = May 23 (Saturday) +1 = May 24 (Sunday of the week). May 26 - 3 (day of week) = May 23 ... etc May 27 - 4 (day of week) = May 23

So, by also including the original date field in question, you can see the real date too all in one SQL call...