您应该向我们提供有关数据结构的更多信息。假设你的[Date]
列的类型datetime
,并且您希望所有日期/周总计/月总计在一列中:
set datefirst 7
select
case
when grouping(cast(datepart(week, [Date]) as varchar(255)))=1 then '<monthtotal>'
when grouping(cast([Date] as date))=1 then '<weektotal>'
else cast(cast([Date] as date) as varchar(255))
end as Period
,CallsNotRecieved = sum(CallsNotRecieved)
,NumberOfCalls = sum(numberofCalls)
from <yourtable>
group by
grouping sets(
(cast(datepart(month, [Date]) as varchar(255)), cast(datepart(week, [Date]) as varchar(255)),cast([Date] as date)),
(cast(datepart(month, [Date]) as varchar(255)), cast(datepart(week, [Date]) as varchar(255))),
(cast(datepart(month, [Date]) as varchar(255)))
)
您可以弄清楚如何做到这一点,而无需set datefirst
,插入周/月数字而不是 weektotal 和 Monthtotal,并确保数据集通过使用进行排序order by grouping()
, and order by [Period]
编辑:
在sql server 2005中,因为没有grouping sets
,人们必须使用WITH ROLLUP
(或者更糟 -WITH CUBE
) 子句,然后过滤掉不需要的分组组合grouping(<column_name>)=1