SQL Server 查询 - 计算房间的可用性

本文介绍了SQL Server 查询 - 计算房间的可用性的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我有一个表名 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 查询 - 计算房间的可用性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！