sql - Calculate a value between 2 dates -

i got in table:

id  datefrom   hours 1  2013-02-01   6 2  2013-04-01   8 

the hours represent hours during month, starting @ date, valid until next record.

i need know how sum hours month between 2 dates.

for example if range dates 2013-02-01 2013-06-01:

6hs february + 6hs march + 8hs april +  8hs may + 8hs june ======== 36 hs 

declare @startdate date declare @enddate date  set @startdate = '20130201' set @enddate   = '20130601'  ;with cte_months  (     select @startdate dt     union     select dateadd(mm,1,dt) cte_months       dateadd(mm,1,dt) <= @enddate ) ,cte_rn  (     select *, row_number() on (partition dt order datefrom desc) rn     cte_months m     left join table1 t on m.dt >= t.datefrom  ) select sum(hours)  cte_rn rn = 1 

first recursive cte find gaps between 2 dates, second cte using row_number , join on actual table find hours each month. @ end, sum where rn=1

sqlfiddle demo