基于日期范围总结每个月的天数

Question

作为一名主要使用C#的开发者，当我试图创建一个基于纯T-SQL的解决方案来汇总一组日期范围内的天数/月份时，我感到有些困惑。

我有一组数据，看起来像这样：

UserID  Department  StartDate   EndDate
======  ==========  ==========  ==========
1       A           2011-01-02  2011-01-05
1       A           2011-01-20  2011-01-25
1       A           2011-02-25  2011-03-05
1       B           2011-01-21  2011-01-22
2       A           2011-01-01  2011-01-20
3       C           2011-01-01  2011-02-03

日期范围不重叠，可能跨越几个月，可能存在一个月内某个特定用户和部门的多个范围。我想做的是总结每个用户、部门、年份和月份的天数（包括），就像这样（对于我的示例中可能存在的任何数学错误，请保留保留意见...）：

UserID  Department  Year  Month  Days
======  ==========  ====  =====  ====
1       A           2011  01     10
1       A           2011  02     4
1       A           2011  03     5
1       B           2011  01     2
2       A           2011  01     20
3       C           2011  01     31
3       C           2011  02     3

这些数据将被放入一个新表格，供报告工具使用。 希望问题描述足够清晰，这是我第一次在这里发布，请温柔一点 :-) 谢谢！

- Jakob Möllås

Answer 1

工作示例

-- sample data in a temp table
declare @t table (UserID int, Department char(1), StartDate datetime, EndDate datetime)
insert @t select
1 ,'A', '2011-01-02','2011-01-05'union all select
1 ,'A', '2011-01-20','2011-01-25'union all select
1 ,'A', '2011-02-25','2011-03-05'union all select
1 ,'B', '2011-01-21','2011-01-22'union all select
2 ,'A', '2011-01-01','2011-01-20'union all select
3 ,'C', '2011-01-01','2011-02-03'

-- the query you need is below this line    

select UserID, Department,
    YEAR(StartDate+v.number) Year,
    MONTH(StartDate+v.number) Month, COUNT(*) Days
from @t t
inner join master..spt_values v
  on v.type='P' and v.number <= DATEDIFF(d, startdate, enddate)
group by UserID, Department, YEAR(StartDate+v.number), MONTH(StartDate+v.number)
order by UserID, Department, Year, Month

- RichardTheKiwi

---

谢谢！运行良好，使用 master..spt_values 很有趣，以前没有遇到过这个系统表。可以在这里找到更多关于 master..spt_values 的信息。 - Jakob Möllås