请教指定日期范围的周统计功能

[hao_cool]

PO订单表 PoDate OrderId Total CustomerId
2017-01-12 1100 26.8 601
2017-05-31 1101 50.2 602
2017-06-10 1102 18.6 603
2017-06-15 1103 107.8 604


假如传入参数
起始：2017-06-02 结束：2017-06-16
这两个日期之间的周数
周一 - 周日
05-29 ~ 06-04
06-05 ~ 06-11
06-12 ~ 06-18

现在想根据06-04，06-11，06-18这个日期来进行分组查询，谢谢

[二月十六]

--测试数据
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([PoDate] Date,[OrderId] int,[Total] decimal(18,7),[CustomerId] int)
Insert #T
select '2017-01-12',1100,26.8,601 union all
select '2017-05-31',1101,50.2,602 union all
select '2017-06-10',1102,18.6,603 union all
select '2017-06-15',1103,107.8,604
Go
--测试数据结束
SET DATEFIRST 1;
DECLARE @begin DATETIME = '2017-06-02';
DECLARE @end DATETIME = '2017-06-16';
SELECT  begintime ,
        t.endtime ,
        SUM(Total) AS Total
FROM    #T
        JOIN ( SELECT   DATEPART(WEEK, DATEADD(DAY, number, @begin)) AS weekname ,
                        MIN(DATEADD(DAY,
                                    1 - ( DATEPART(dw,
                                                   DATEADD(DAY, number, @begin)) ),
                                    DATEADD(DAY, number, @begin))) AS begintime ,
                        MAX(DATEADD(DAY,
                                    7 - ( DATEPART(dw,
                                                   DATEADD(DAY, number, @begin)) ),
                                    DATEADD(DAY, number, @begin))) AS endtime
               FROM     master.dbo.spt_values
               WHERE    type = 'P'
                        AND number BETWEEN 0 AND DATEDIFF(DAY, @begin, @end)
               GROUP BY DATEPART(WEEK, DATEADD(DAY, number, @begin))
             ) t ON PoDate BETWEEN t.begintime AND t.endtime
GROUP BY begintime ,
        t.endtime;

[道素]

if not object_id(N'Tempdb..#tb') is null drop table #tb
create table #tb([PoDate] Date,[OrderId] int,[Total] decimal(18,7),[CustomerId] int)
Insert #tb
select '2017-01-12',1100,26.8,601 union all
select '2017-05-31',1101,50.2,602 union all
select '2017-06-10',1102,18.6,603 union all
select '2017-06-15',1103,107.8,604
go

declare @st date='2017-06-02',@ed date='2017-06-16'
declare @backdays int=2-(datepart(dw,@st)+@@datefirst)%7
declare @forwddays int=8-(datepart(dw,@ed)+@@datefirst)%7
select @@datefirst, @backdays,@forwddays
--set datefirst 2
select datepart(wk,dateadd(d,sv.number+@backdays-1,@st) ) as ID
	      ,min(dateadd(d,sv.number+@backdays,@st)) as 周一
	      ,max(dateadd(d,sv.number+@backdays,@st)) as 周日
	      ,sum(ISNULL(tb.[Total],0)) as [Total]
	      --,dateadd(d,sv.number+@backdays,@st) as d
	      --,case (datepart(dw,dateadd(d,sv.number+@backdays,@st))+@@datefirst)%7 when 2 then N'周一' when 1 then N'周日' when 3 then N'周二' when 4 then N'周三' when 5 then N'周四' when 5 then N'周五' else N'周六' end as wk
from master.dbo.spt_values as sv
left join #tb as tb on datediff(d,tb.[PoDate],dateadd(d,sv.number+@backdays,@st))=0
where sv.type='P' and sv.number between 0 and datediff(d,@st,@ed)+@forwddays-@backdays
group by datepart(wk,dateadd(d,sv.number+@backdays-1,@st) )

+----+------------+------------+-------+
| ID | 周一         | 周日         | Total |
+----+------------+------------+-------+
| 22 | 2017-05-29 | 2017-06-04 | 50.2  |
| 23 | 2017-06-05 | 2017-06-11 | 18.6  |
| 24 | 2017-06-12 | 2017-06-18 | 107.8 |
+----+------------+------------+-------+