Dynamic update for different columns using between two date like cal, Sql Server

Language: Layout:

create table roommaster (roomid int not null identity(1,1), roomtypeid int, roommasterstatus int);
insert into roommaster (roomtypeid,roommasterstatus) values (0,99),(0,0),(0,0);
create table roomcheckindetails (roomid int, checkindate date, checkoutdate date);
insert into roomcheckindetails values (1,'20170605','20170607'),(2,'20170605','20170608');
create table roombookingdetails (roomid int, ExpectedCheckinDate date, ExpectedCheckoutDate date);
insert into roombookingdetails values (1,'20170605','20170607'),(2,'20170605','20170608'),(3,'20170606','20170609');

declare @fromdate date = '20170605';
declare @thrudate date = dateadd(day,6,@fromdate)

;with n as (select n from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t(n))
, dates as (
  select top (datediff(day, @fromdate, @thrudate)+1) 
      [Date]=convert(date,dateadd(day,row_number() over(order by (select 1))-1,@fromdate))
    , rn = row_number() over(order by (select 1))
  from n as deka cross join n as hecto cross join n as kilo
                 cross join n as tenK cross join n as hundredK
   order by [Date]
)
, cte as (
select 
    rm.roomid
  , d.rn
  , Value = case
      when rcd.roomid is not null then 1
      when rbd.roomid is not null then 8
      else 0
      end
from dates d
  cross join roommaster rm
  left join roomcheckindetails rcd
    on rm.roomid = rcd.roomid 
   and d.date >= rcd.checkindate 
   and d.date <= rcd.checkoutdate
  left join roombookingdetails rbd
    on rm.roomid = rbd.roomid
   and d.date >= rbd.expectedcheckindate 
   and d.date <= rbd.expectedcheckoutdate
where rm.roommasterstatus <> 99
)
select 
    roomid
  , Day1 = min(case when rn = 1 then value end)
  , Day2 = min(case when rn = 2 then value end)
  , Day3 = min(case when rn = 3 then value end)
  , Day4 = min(case when rn = 4 then value end)
  , Day5 = min(case when rn = 5 then value end)
  , Day6 = min(case when rn = 6 then value end)
  , Day7 = min(case when rn = 7 then value end)
from cte
group by roomid

View schema
Execution time: 0,11 sec, rows selected: 4, rows affected: 8, absolute service time: 0,27 sec