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
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…