在 SQL Server 2008 R2 中选择具有后续日期字段的开始日期和结束日期表单记录

Question

>我在SQL Server 2008 R2中有一个名为ReserveLog的表。这是一个现有表，用于存储综合大楼中每个房间的预订日期。

它是这样的：

RoomNumber ReserveDate
----------------------
1          2017-07-01
1          2017-07-02 
1          2017-07-03
1          2017-07-06
1          2017-07-07
1          2017-07-08
2          2017-01-02
2          2017-01-03
2          2017-01-04
2          2017-01-09
2          2017-01-10

我想查询此表，以便得到以下结果：

RoomNumber ReserveStartDate ReserveEndDate
------------------------------------------
1            2017-07-01       2017-07-03
1            2017-07-06       2017-07-08
2            2017-07-02       2017-07-04
2            2017-07-09       2017-07-10

可能吗？我无法决定如何做到这一点。任何帮助都提前表示感谢

Answer 1

create table #reservs
(
roomnumber INT, ReserveDate DATE
)

INSERT INTO #reservs VALUES (1,          '2017-07-01');
INSERT INTO #reservs VALUES (1,          '2017-07-02');
INSERT INTO #reservs VALUES (1,          '2017-07-03');
INSERT INTO #reservs VALUES (1,          '2017-07-06');
INSERT INTO #reservs VALUES (1,          '2017-07-07');
INSERT INTO #reservs VALUES (1,          '2017-07-08');
INSERT INTO #reservs VALUES (2,          '2017-01-02');
INSERT INTO #reservs VALUES (2,          '2017-01-03');
INSERT INTO #reservs VALUES (2,          '2017-01-04');
INSERT INTO #reservs VALUES (2,          '2017-01-09');
INSERT INTO #reservs VALUES (2,          '2017-01-10');
select roomnumber, MIN(reservedate) as mn, MAX(reservedate) as mx
FROM (
SELECT *
, DATEDIFF(day, ROW_NUMBER() OVER(partition by roomnumber order by reservedate) ,reservedate) as ind
FROM #reservs
) a
group by roomnumber, ind
order by 1, 2

Answer 2

使用通用表表达式、行注释和 SQL 小提琴链接尝试此操作：

SQL 小提琴

create table Reservelog
(
RoomNumber INT,
ReserveDate Date
)
INSERT INTO ReserveLog
VALUES
(1, '2017-07-01'),
(1, '2017-07-02'), 
(1, '2017-07-03'),
(1, '2017-07-06'),
(1, '2017-07-07'),
(1, '2017-07-08'),
(2, '2017-01-02'),
(2, '2017-01-03'),
(2, '2017-01-04'),
(2, '2017-01-09'),
(2, '2017-01-10')

查询 1：

;WITH CTE
As
(
SELECT *, 
(
-- Get Previous Reserve Date for this room
SELECT TOP 1 ReserveDate 
FROM ReserveLog R2 
WHERE R1.RoomNumber = R2.RoomNumber AND 
R1.ReserveDate > R2.ReserveDate 
ORDER BY ReserveDate DESC
) As PrevReserveDate,
(
-- Get NExt ReserveDate For this room
SELECT TOP 1 ReserveDate 
FROM ReserveLog R2 
WHERE R1.RoomNumber = R2.RoomNumber AND 
R1.ReserveDate < R2.ReserveDate 
ORDER BY ReserveDate
) As NextReserveDate
FROM ReserveLog R1
),
CTE2
AS
(
SELECT *,
CASE             
WHEN PrevReserveDate IS NULL OR 
DATEDIFF(D, PrevReserveDate, ReserveDate ) > 1 
THEN 1 -- Flag as a StartDate 
ELSE 0 
END As DateStart,
CASE 
WHEN NextReserveDate IS NULL OR 
DATEDIFF(D, ReserveDate, NExtReserveDate) > 1 
THEN 1  -- Flag as an end date
ELSE 0 
END As DateEnd,
ROW_NUMBER() OVER 
(PARTITION BY RoomNumber ORDER BY ReserveDate) AS RN
FROM CTE
-- only select rows which have no previous or next reservation or 
-- ones where the difference between consecutive reservations > 1 day
WHERE PrevReserveDate IS NULL OR 
NextReserveDate IS NULL OR 
DATEDIFF(D, PrevReserveDate, ReserveDate ) > 1 OR 
DATEDIFF(D, ReserveDate, NExtReserveDate) > 1
)
SELECT startRows.RoomNumber, 
startRows.ReserveDate As ReserveStartDate, 
endRows.ReserveDate As ReserveEndDate
FROM CTE2 startRows
INNER JOIN  CTE2 endRows
ON startRows.RN + 1 = endRows.RN  AND 
startRows.RoomNumber = endRows.RoomNumber AND 
endRows.DateEnd = 1
WHERE startRows.DateStart = 1

结果：

| RoomNumber | ReserveStartDate | ReserveEndDate |
|------------|------------------|----------------|
|          1 |       2017-07-01 |     2017-07-03 |
|          1 |       2017-07-06 |     2017-07-08 |
|          2 |       2017-01-02 |     2017-01-04 |
|          2 |       2017-01-09 |     2017-01-10 |

Answer 3

使用此查询：

SELECT * FROM R2 WHERE ReserveDate between ('2017-07-01 ' AND '2017-07-03');