sql - How to split the weeks equally between the year change

Question

The query worked fine in generating weeks until the year change. As you can see in the generated output (given in the following image) during year change I got a different pattern. Please help. I want equal split of 7 days. At the moment order is the order is not equal as you can see in the output 28/12/2014, 01/01/2015 and 04/01/2015 are in wrong order. Similarly the first difference is not 7 days but 6 days ( from 04/09/2014 to 09/11/2014). I want this to be 7 days to i.e. ( from 03/09/2014 to 09/11/2014)

Answer 1

you could use a cte to generate the 14 weeks

This will generate a table that has the weeks in 7 day increments, regardless of the start date, which you could then use to classify your data:

declare @startdate date
set @startdate = '2014-11-13'

;with cte (SOW,EOW,cnt) 
as
    (
    select @startdate as SOW,dateadd(dd,6,@startdate) as EOW, 1 as cnt
    union all
    select dateadd(dd,7,SOW),dateadd(dd,7,EOW),cnt+1
    from cte
    where cnt<14
    )
select * from cte