Summing days per month based on date ranges

Question

I am primarily a C # developer, I scratch my head trying to create a clean T-SQL-based solution for the problem of summing days / months based on a set of date ranges.

I have a dataset that looks something like this:

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

Date ranges do not overlap, can last several months, within one month there may be several ranges for a specific user and department. What I would like to do is to sum the number of days (inclusively) for each user, department, year and month, for example (with redundancy for any mathematical errors in my example ...):

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

, . , , , : -)

!

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