Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
418 views
in Technique[技术] by (71.8m points)

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

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)

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...