Пробелы и острова

Question

У меня есть таблица с StartDate и EndDate. Я хотел получить недостающую дату, и сценарий, который у меня есть, делает это. Но если учесть эти данные:

LocationID  StartDate   EndDate
----------  ----------  ----------  
1           2020-01-01  2020-01-03  
1           2020-01-04  2020-01-05  
1           2020-01-10  2020-01-15  

Этот запрос:

DECLARE @t table(PlaceID int, StartDate date, EndDate date);

INSERT @t(PlaceID, StartDate, EndDate) VALUES
(1,'20200101','20200103'),(1,'20200104','20200105'),(1,'20200110','20200115');

--(2,'20200103','20200106'),(2,'20200107','20200110'),(2,'20200120','20200123');

-- input parameters 
DECLARE @PlaceIDofInterest   int  = 1,
        @StartDateOfInterest date = '20200101', 
        @EndDateOfInterest   date = '20200131';
    
;WITH date_range(d) AS -- the entire range of days we care about
(
  SELECT @StartDateOfInterest UNION ALL
  SELECT DATEADD(DAY, 1, d) FROM date_range
         WHERE d < @EndDateOfInterest
),
islands AS -- grouped sets of days _not_ covered
(
  SELECT r.d, island = DATEADD(DAY, DENSE_RANK() OVER (ORDER BY r.d) * -1, r.d) 
    FROM date_range AS r
    LEFT OUTER JOIN @t AS t
      ON  r.d >= t.StartDate 
      AND r.d <= t.EndDate
      AND t.PlaceID = @PlaceIDofInterest
    WHERE t.PlaceID IS NULL
)
SELECT  
      MIN(d) as STARTDATE , 
      MAX(d) as ENDDATE-- for each island, grab the start and end
  FROM 
      islands 
 
  GROUP BY island 
  ORDER BY MIN(d);

Возвращает этот вывод:

STARTDATE   ENDDATE  
---------   ----------    
2020-01-06  2020-01-09  
2020-01-16  2020-01-31  

Но мне нужен был результат:

STARTDATE   ENDDATE
---------   ----------    
2020-01-03  2020-01-04  
2020-01-05  2020-01-10  
2020-01-15  2020-01-31  

Какие изменения я могу внести?

Отредактировано для добавления: место можно забронировать с ночи до утра. Итак, StartDate Night - EndDate Утро. ТАК, Место будет бесплатным в ночь на EndDate, если оно не забронировано. Также локация будет бесплатной утром начала.

С 1 по 3 января забронированы номера, то есть с 1 ночи до 3 утра. следующее бронирование только на 4-ю ночь. Так что с 3 ночи до 4 утра это место тоже свободно. Поэтому мне нужно, чтобы результат имел 3-ю дату начала и 4-е января в качестве конечной даты в одной строке. Надеюсь, это ясно.

Answer 1

Вам действительно не нужен компьютер для этого

Оконная функция LEAD может делать то, что вам нужно

Его можно уточнить, если даты соответствуют

CREATE table t1(PlaceID int, StartDate date, EndDate date);

GO

INSERT INTO  t1 (PlaceID, StartDate, EndDate) VALUES
(1,'20200101','20200103')
,(1,'20200104','20200111')
,(1,'20200111','20200113')
,(1,'20200114','20200121')
,(1,'20200121','20200123')
,(2,'20200103','20200106')
,(2,'20200107','20200110')
,(2,'20200120','20200123');
GO

SELECT
PlaceID
,EndDate
,Nextdate
FROM
(SELECT
PlaceID
,EndDate,
COALESCE(DATEADD(DAY,0,lead(StartDate,1,NULL)  OVER (partition by PlaceID ORDER BY StartDate)),EOMONTH(StartDate)) AS Nextdate
FROM t1) t2
WHERE EndDate != Nextdate
GO

PlaceID | EndDate | Следующая дата  
------: | : --------- | : ---------
      1 | 2020-01-03 | 2020-01-04
      1 | 2020-01-13 | 2020-01-14
      1 | 2020-01-23 | 2020-01-31
      2 | 2020-01-06 | 2020-01-07
      2 | 2020-01-10 | 2020-01-20
      2 | 2020-01-23 | 2020-01-31

db <> скрипка здесь

Answer 2

Ответ @nbk даст вам то, что вы хотите, я просто помещаю это здесь, так как это может оказаться для вас интересным; хотя это не полное. На данный момент он получает список возможных дат бронирования и показывает, забронировано оно или нет, в соответствии с изложенными вами правилами. Это может быть полезно в качестве календаря или чего-то подобного для вас.

DECLARE @Source TABLE
    (
    LocationID INT NOT NULL
    , StartDate DATE NOT NULL
    , EndDate DATE NOT NULL
    )

INSERT INTO @Source 
    (LocationID, StartDate, EndDate)
VALUES (1, '1/1/2020', '1/3/2020')
    , (1, '1/4/2020', '1/5/2020')
    , (1, '1/10/2020', '1/15/2020')

DECLARE @LocationOfInterest INT = 1
DECLARE @StartDate DATE = '1/1/2020'
DECLARE @EndDate DATE = '1/31/2020'

;WITH CTE_I AS
    (
    SELECT *
        , PriorEndDate = LAG(EndDate) OVER (PARTITION BY LocationID ORDER BY StartDate)
        , NextStart = LEAD(StartDate) OVER (PARTITION BY LocationID ORDER BY StartDate)
    FROM @Source AS S
    WHERE LocationID = @LocationOfInterest
    )
, CTE_G AS
    (
    SELECT I.LocationID
        , I.StartDate
        , I.EndDate
        , I.PriorEndDate 
        , I.NextStart
        , IslandStart = COALESCE(PriorEndDate, EndDate)
        , IslandEnd = I.NextStart 
    FROM CTE_I AS I
    )
SELECT * FROM CTE_G


;WITH CTE_Calendar AS
    (
    SELECT @StartDate AS D
    UNION ALL
    SELECT DATEADD(DAY, 1, D)
    FROM CTE_Calendar
    WHERE D < @EndDate
    )
, CTE_BookingCalendar AS
    (
    SELECT C.D AS BookingStart 
        , DATEADD(DAY, 1, C.D) AS BookingEnd
    FROM CTE_Calendar AS C
    )
, CTE_WhatIsBooked AS
    (
    SELECT B.BookingStart
        , B.BookingEnd
        , S.LocationID
        , S.StartDate
        , S.EndDate
    FROM CTE_BookingCalendar AS B
        LEFT OUTER JOIN @Source AS S ON B.BookingStart BETWEEN S.StartDate AND S.EndDate AND S.EndDate >= B.BookingEnd
    WHERE S.LocationID IS NULL OR S.LocationID = @LocationOfInterest
    ORDER BY B.BookingStart
    )