我有以下几点：
http://sqlfiddle.com/#!6/226ae/1
我现在试图在一年的每个星期添加一行,并相应地过滤联系人. CONTACTS有一个datetime列.新表将如下所示：
Status 1 Status 2 Status 3 Week 1 3 4 2 Week 2 1 5 3 Week 3 2 2 4
我认为需要使用DATEADD,但是我如何开始改变我的查询就失去了意义.
解决方法
您可以使用
DATEPART(),如果您拥有跨越多年的数据,则该组会在一周和一年中使用：
SELECT
'Week ' + cast(datepart(wk,created) as varchar(2)) Week,SUM(case WHEN status = 1 then 1 else 0 end) Status1,SUM(case WHEN status = 2 then 1 else 0 end) Status2,SUM(case WHEN status = 3 then 1 else 0 end) Status3,SUM(case WHEN status = 4 then 1 else 0 end) Status4,SUM(case WHEN status = 5 then 1 else 0 end) Status5
FROM contacts
group by datepart(wk,created),year(created)
将年份添加到最终结果中：
SELECT
'Week ' + cast(datepart(wk,year(created) year,SUM(case WHEN status = 5 then 1 else 0 end) Status5
FROM contacts
group by datepart(wk,year(created)
