sql - Query to total monthly hours for events spanning month borders -

i have series of events unique , may continue arbitrary time. need calculate how may hours events contribute monthly totals, given event may commence before specific month (in case contribute previous month current month) , finish after specific month.

the data looks like:

event | startdate | enddate   | ------------------------------- | 1   | 10/01/2015| 11/01/2015| | 2   | 20/12/2014| 9/01/2015 | | 3   | 25/01/2015| 14/02/2015| 

ultimately, try generate list grouped month , year totals "event hours" each month (not each event - events within month summed).

to honest, not sure start this.

the easy way using months table because can have empty months.

create table months (    month_id integer,    date_ini datetime,    date_end datetime )  

then join table.

sql fiddle demo

with ranges (     select *              months m          left join events e              on   e.startdate <= m.d_end             ,  e.enddate >= m.d_begin  )  select r.*,         datediff(hour,                  case                      when startdate > d_begin startdate                     when startdate null null                     else d_begin                 end,                  case                      when enddate < d_end enddate                     when enddate null null                     else dateadd(day,1,d_end)                 end) hours ranges r 

you have 4 cases

• a event begin , end inside month
• a event end beyond month end
• a event start before , end after month
• a month no events.