sql server - Combine date intervals from 2 tables -

i have daily storage room temperature measurements in table (separate table each room):

 begindate | enddate  | temperature | room_id -------------------------------------------- 1/1/2014  | 1/2/2014 | 10          |  1 1/2/2014  | 1/3/2014 | 12          |  1 1/4/2014  | 1/5/2014 | 11          |  1 1/5/2014  | 1/6/2014 | 12          |  1 1/6/2014  | 1/7/2014 | 10          |  1 1/7/2014  | 1/8/2014 | 11          |  1 1/8/2014  | 1/9/2014 | 12          |  1 ...... 1/29/2014 | 1/30/2014| 10          |  1 1/30/2014 | 1/31/2014| 12          |  1 1/31/2014 | 2/1/2014 | 10          |  1 ...... 3/1/2014  | 3/2/2014 | 14          |  1 ..... not show dates measurements daily 

the second table contains dates when storage room occupied

 begindate  |  enddate  | room_id --------------------------------- 1/6/2014   | 1/9/2014  |  1 1/15/2014  | 1/29/2014 |  1 3/1/2014   | 3/2/2014  |  1 

what need average room temperatures during time when occupied or monthly temperatures in case room not occupied or occupied greater month interval. above data result should like:

 begindate    |   enddate   |  avg_temp  | room_id -------------------------------------------------- 1/1/2014     |   1/6/2014  |    11      |   1 1/6/2014     |   1/9/2014  |    11      |   1 1/9/2014     |   1/15/2014 |    11      |   1 1/15/2014    |   1/29/2014 |    12      |   1 1/29/2014    |   2/1/2014  |    11      |   1 2/1/2014     |   3/1/2014  |    13      |   1 3/1/2014     |   3/2/2014  |    14      |   1 3/2/2014     |   4/1/2014  |    15      |   1    

i tried several ways every time duplicate overlapping dates or missing date intervals. appreciated.

code create temperature data table:

 insert @temps values ('01/01/2014','01/02/2014','10','1'), ('01/02/2014','01/03/2014','12','1'), ('01/03/2014','01/04/2014','11','1'), ('01/04/2014','01/05/2014','11','1'), ('01/05/2014','01/06/2014','12','1'), ('01/06/2014','01/07/2014','10','1'), ('01/07/2014','01/08/2014','11','1'), ('01/08/2014','01/09/2014','12','1'), ('01/09/2014','01/10/2014','10','1'), ('01/10/2014','01/11/2014','12','1'), ('01/11/2014','01/12/2014','10','1'), ('01/12/2014','01/13/2014','10','1'), ('01/13/2014','01/14/2014','11','1'), ('01/14/2014','01/15/2014','12','1'), ('01/15/2014','01/16/2014','11','1'), ('01/16/2014','01/17/2014','14','1'), ('01/17/2014','01/18/2014','12','1'), ('01/18/2014','01/19/2014','10','1'), ('01/19/2014','01/20/2014','11','1'), ('01/20/2014','01/21/2014','10','1'), ('01/21/2014','01/22/2014','11','1'), ('01/22/2014','01/23/2014','12','1'), ('01/23/2014','01/24/2014','11','1'), ('01/24/2014','01/25/2014','14','1'), ('01/25/2014','01/26/2014','12','1'), ('01/26/2014','01/27/2014','10','1'), ('01/27/2014','01/28/2014','11','1'), ('01/28/2014','01/29/2014','10','1'), ('01/29/2014','01/30/2014','11','1'), ('01/30/2014','01/31/2014','12','1'), ('01/31/2014','02/01/2014','11','1'), ('02/01/2014','02/02/2014','14','1'), ('02/02/2014','02/03/2014','12','1'), ('02/03/2014','02/04/2014','10','1'), ('02/04/2014','02/05/2014','11','1'), ('02/05/2014','02/06/2014','10','1'), ('02/06/2014','02/07/2014','11','1'), ('02/07/2014','02/08/2014','12','1'), ('02/08/2014','02/09/2014','11','1'), ('02/09/2014','02/10/2014','14','1'), ('02/10/2014','02/11/2014','12','1'), ('02/11/2014','02/12/2014','10','1'), ('02/12/2014','02/13/2014','11','1'), ('02/13/2014','02/14/2014','10','1'), ('02/14/2014','02/15/2014','11','1'), ('02/15/2014','02/16/2014','12','1'), ('02/16/2014','02/17/2014','11','1'), ('02/17/2014','02/18/2014','14','1'), ('02/18/2014','02/19/2014','12','1'), ('02/19/2014','02/20/2014','10','1'), ('02/20/2014','02/21/2014','11','1'), ('02/21/2014','02/22/2014','10','1'), ('02/22/2014','02/23/2014','11','1'), ('02/23/2014','02/24/2014','12','1'), ('02/24/2014','02/25/2014','11','1'), ('02/25/2014','02/26/2014','14','1'), ('02/26/2014','02/27/2014','12','1'), ('02/27/2014','02/28/2014','15','1'), ('02/28/2014','03/01/2014','11','1'), ('03/01/2014','03/02/2014','11','1'), ('03/02/2014','03/03/2014','12','1'), ('03/03/2014','03/04/2014','11','1'), ('03/04/2014','03/05/2014','14','1'), ('03/05/2014','03/06/2014','12','1'), ('03/06/2014','03/07/2014','15','1'), ('03/07/2014','03/08/2014','11','1'), ('03/08/2014','03/09/2014','12','1'), ('03/09/2014','03/10/2014','11','1'), ('03/10/2014','03/11/2014','14','1'), ('03/11/2014','03/12/2014','12','1'), ('03/12/2014','03/13/2014','15','1'), ('03/13/2014','03/14/2014','11','1'), ('03/14/2014','03/15/2014','12','1'), ('03/15/2014','03/16/2014','11','1'), ('03/16/2014','03/17/2014','14','1'), ('03/17/2014','03/18/2014','12','1'), ('03/18/2014','03/19/2014','15','1'), ('03/19/2014','03/20/2014','11','1'), ('03/20/2014','03/21/2014','12','1'), ('03/21/2014','03/22/2014','11','1'), ('03/22/2014','03/23/2014','14','1'), ('03/23/2014','03/24/2014','12','1'), ('03/24/2014','03/25/2014','15','1'), ('03/25/2014','03/26/2014','11','1'), ('03/26/2014','03/27/2014','12','1'), ('03/27/2014','03/28/2014','11','1'), ('03/28/2014','03/29/2014','14','1'), ('03/29/2014','03/30/2014','12','1'), ('03/30/2014','03/31/2014','15','1'), ('03/31/2014','04/01/2014','11','1'), ('04/01/2014','04/02/2014','12','1'), ('04/02/2014','04/03/2014','11','1'), ('04/03/2014','04/04/2014','14','1'), ('04/04/2014','04/05/2014','12','1'), ('04/05/2014','04/06/2014','15','1')  

could need?

my approach is, give periods key , use either period or year_month key partition avg-function (over clause: https://msdn.microsoft.com/en-us/library/ms189461.aspx)

edit: new approach, op needs else:

remark: if "free" period must end real period begins , new "free" period must follow after real period's end suggest approach, in 1 answer. please let me know...

edit: "remark" point, suggest approach:

set language english;  declare @rooms table(roomid int,room varchar(10)); insert @rooms values(1,'room 1'),(2,'room 2');  declare @temps table(begd date, endd date,temp int, roomid int); insert @temps  values('1/1/2014','1/2/2014','10','1')      ,('1/2/2014','1/3/2014','12','1')      ,('1/4/2014','1/5/2014','11','1')      ,('1/5/2014','1/6/2014','12','1')      ,('1/6/2014','1/7/2014','10','1')      ,('1/7/2014','1/8/2014','11','1')      ,('1/8/2014','1/9/2014','12','1')      ,('1/29/2014','1/30/2014','10','1')      ,('1/30/2014','1/31/2014','12','1')      ,('1/31/2014','2/1/2014','10','1')      ,('3/1/2014','3/2/2014','14','1');  declare @periods table(periodid int, begd date,endd date,roomid int); insert @periods values(1,'1/6/2014','1/9/2014','1')      ,(2,'1/15/2014','1/29/2014','1')      ,(3,'3/1/2014','3/2/2014','1');  declare @startdate date='1/1/2014'; declare @daycount int=50; daylist (     select top (@daycount) dateadd(d,numbers.nr-1,@startdate) daydate         (         select row_number() over(order o.object_id) sys.objects o     ) numbers(nr) ) ,roomdaylist (     select daylist.daydate,r.*      daylist,@rooms r  ) ,occupations (     select roomdaylist.daydate           ,roomdaylist.roomid           ,roomdaylist.room           ,isnull(inperiod.periodid,'free') periodid     roomdaylist     outer apply     (         select 'p'+cast(periodid varchar(max)) periodid         @periods p         p.roomid=roomdaylist.roomid           , roomdaylist.daydate between p.begd , p.endd        ) inperiod ) ,periodborders (     select tbl.*         (         select occupations.*               ,case when occupations.daydate=@startdate @startdate else case when occupations.periodid<>rowbefore.periodid occupations.daydate else null end end firstdayofperiod               ,case when occupations.daydate=(select max(daydate) daylist) (select max(daydate) daylist) else case when occupations.periodid<>rowafter.periodid occupations.daydate else null end end lastdayofperiod         occupations         outer apply         (             select periodid             occupations inneroc             inneroc.daydate=dateadd(d,-1,occupations.daydate)               , inneroc.roomid=occupations.roomid         ) rowbefore         outer apply         (             select periodid             occupations inneroc             inneroc.daydate=dateadd(d,1,occupations.daydate)               , inneroc.roomid=occupations.roomid         ) rowafter     ) tbl     tbl.firstdayofperiod not null or tbl.lastdayofperiod not null ) ,mergeborders (     select periodborders.daydate           ,periodborders.roomid           ,periodborders.room           ,periodborders.periodid           ,periodborders.firstdayofperiod           ,isnull(periodborders.lastdayofperiod,periodenddate.dt) lastdayofperiod     periodborders     cross apply     (         select top 1 pb.lastdayofperiod periodborders pb pb.roomid=periodborders.roomid , pb.daydate>periodborders.daydate order pb.daydate asc     ) periodenddate(dt)     periodborders.firstdayofperiod not null ) ,tempmeasures (     select t.temp           ,period.*     @temps t     cross apply     (         select *          mergeborders         mergeborders.roomid=t.roomid , t.begd between mergeborders.firstdayofperiod , mergeborders.lastdayofperiod     ) period ) select t.roomid       ,t.firstdayofperiod       ,t.lastdayofperiod       ,avg(t.temp) tempmeasures t group t.firstdayofperiod,t.lastdayofperiod,t.roomid order t.roomid,t.firstdayofperiod