CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008-r2sql-server-2012
user2069823
user2069823

Reputation: 47

SQL Server query - calculate availability of rooms

I have a table name RoomInventory that has data like 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 which I want is like 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..

Upvotes: 0

Views: 148

Answers (3)

James Z
James Z

Reputation: 12317

If there's a lot of data, recursive CTE might not be the best solution. There is also a little trick that you can use. If you use datediff and row_number to group the dates together, you'll get the result this way:

select
  min(bookdate),
  max(bookdate),
  rooms
from (
  select 
    bookdate,
    rooms,
    datediff(day, 0, bookdate) - row_number() 
       over (partition by rooms order by bookdate asc) as DATEGRP
  from
    Reservation
) X
group by
  DATEGRP,
  rooms
order by 1

Here the datediff from day 0 increases always by 1 when bookdate increases by one, and of also row number increases by one, they are consecutive days. Partition by makes sure that only days with same availability get consecutive numbers. Grouping the result with that and using min / max will bring the start and end dates.

SQL Fiddle

Upvotes: 1

Anuj Tripathi
Anuj Tripathi

Reputation: 2281

Try below query

DECLARE @Reservation TABLE ( BookDate DATE, ROOMS INT)
 INSERT INTO @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 AS (
        select ROW_NUMBER() OVER(ORDER BY BookDate) AS RowNumber,
        [ROOMS], BookDate FROM @Reservation
    ),
    cte2 as (
        SELECT TOP 1 RowNumber, 1 as GroupNumber, [ROOMS], BookDate FROM cte ORDER BY RowNumber
        UNION ALL
        SELECT c1.RowNumber,
            CASE WHEN c2.[ROOMS] <> c1.[ROOMS] then c2.GroupNumber + 1 ELSE c2.GroupNumber END AS  GroupNumber, c1.[ROOMS], c1.BookDate 
        FROM cte2 c2 join cte c1 on c1.RowNumber = c2.RowNumber + 1     
    )
    SELECT Start_Date, End_Date, Rooms
    FROM
    (   SELECT MIN(BookDate) AS START_DATE, MAX(BookDate) AS END_DATE ,ROOMS, GroupNumber
        FROM cte2
        GROUP BY  ROOMS ,GroupNumber
    ) a

SQLFiddler Demo

Upvotes: 3

logbook
logbook

Reputation: 45

At first glance I would suggest GROUP BY 'RoomsAvailable' whilst the SELECT would be something like MIN(Date) StartDate, MAX(Date) EndDate

Upvotes: -2

Related Questions