SQL Server 查询 - 计算房间的可用性 [英] SQL Server query - calculate availability of rooms

问题描述

我有一个表名 RoomInventory 有如下数据

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 

我想要的结果如下:

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 

请帮忙..

推荐答案

试试下面的查询

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 演示

这篇关于SQL Server 查询 - 计算房间的可用性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！