SQL Server query - calculate availability of rooms -

i have table name roominventory has data below

date (date)                 roomsavailable (int) 1-jul-2015                           30   2-jul-2015                           30  3-jul-2015                           30  5-jul-2015                           28  6-jul-2015                           28  7-jul-2015                           28  8-jul-2015                           30  9-jul-2015                           30  10-jul-2015                          26  11-jul-2015                          28  12-jul-2015                          28  

the result want below:

startdate       enddate         roomsavailable   ----------------------------------------------  1-jul-2015      3-jul-2015          30   5-jul-2015      7-jul-2015          28   8-jul-2015      9-jul-2015          30  10-jul-2015     10-jul-2015          26  11-jul-2015     12-jul-2015          28  

please help..

try below query

declare @reservation table ( bookdate date, rooms int)  insert @reservation values  ('1-jul-2015',30 ),  ('2-jul-2015',30 ), ('3-jul-2015',30 ), ('5-jul-2015',28 ), ('6-jul-2015',28 ), ('7-jul-2015',28 ), ('8-jul-2015',30 ), ('9-jul-2015',30 ), ('10-jul-2015',26 ), ('11-jul-2015',28 ), ('12-jul-2015',28 )       ;with     cte (         select row_number() over(order bookdate) rownumber,         [rooms], bookdate @reservation     ),     cte2 (         select top 1 rownumber, 1 groupnumber, [rooms], bookdate cte order rownumber         union         select c1.rownumber,             case when c2.[rooms] <> c1.[rooms] c2.groupnumber + 1 else c2.groupnumber end  groupnumber, c1.[rooms], c1.bookdate          cte2 c2 join cte c1 on c1.rownumber = c2.rownumber + 1          )     select start_date, end_date, rooms         (   select min(bookdate) start_date, max(bookdate) end_date ,rooms, groupnumber         cte2         group  rooms ,groupnumber     ) 

sqlfiddler demo