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

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

使用常见的表表达式、行中的注释和SQL Fiddle链接尝试此操作:

SQL Fiddle

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')

:

;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

Results

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

使用此查询:

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