grouping by week(按周统计数据)

select orderdate-weeknum+1 as week_start,
orderdate-weeknum+7 as week_end,
count(*) as ordernum
from
(
select datepart(weekday,orderdate+@@datefirst-1) as weeknum,orderdate
from dbo.orders
) as d
group by orderdate-weeknum+1 ,orderdate-weeknum+7

 

 

 

Table 1-10: Weekly Counts of Orders (abbreviated)
Open table as spreadsheet

week_start	week_end	numorders
1996-07-01 00:00:00.000	1996-07-07 00:00:00.000	2
1996-07-08 00:00:00.000	1996-07-14 00:00:00.000	6
1996-07-15 00:00:00.000	1996-07-21 00:00:00.000	6
1996-07-22 00:00:00.000	1996-07-28 00:00:00.000	5
1996-07-29 00:00:00.000	1996-08-04 00:00:00.000	6
1996-08-05 00:00:00.000	1996-08-11 00:00:00.000	5
1996-08-12 00:00:00.000	1996-08-18 00:00:00.000	6
1996-08-19 00:00:00.000	1996-08-25 00:00:00.000	5
1996-08-26 00:00:00.000	1996-09-01 00:00:00.000	6
1996-09-02 00:00:00.000	1996-09-08 00:00:00.000	5