Brechas e islas

Question

Tengo una tabla con StartDate y EndDate. Quería conseguir la fecha que faltaba y el guión que tengo lo hace. Pero si se dan estos datos:

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

Esta consulta:

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

Devuelve esta salida:

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

Pero quería la salida como:

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

¿Qué cambios puedo hacer?

Editado para agregar: Se puede reservar una ubicación desde la noche hasta la mañana. Así que StartDate Night to EndDate Morning. Por lo tanto, la ubicación será gratuita en la noche de EndDate, a menos que esté reservada. Además, la ubicación será gratuita en Startdate Morning.

El 1 de enero al 3 de enero está reservado, es decir, desde la 1ª noche hasta la 3ª mañana. la próxima reserva es solo para la cuarta noche. Así que la ubicación es gratuita desde la tercera noche hasta la cuarta mañana también. Entonces, necesitaría que el resultado tenga el 3er como fecha de inicio y el 4 de enero como fecha de finalización en una fila. Espero que esto quede claro.

Answer 1

Realmente no necesitas un cte para eso

La función de ventana LEAD puede hacer lo que necesita

Se puede refinar si las fechas corresponden

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 | Proxima fecha  
------: | : --------- | : ---------
      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 <> violín aquí

Answer 2

La respuesta de @nbk le dará lo que desea, solo estoy poniendo esto aquí porque puede resultarle interesante; aunque no está completo. Por el momento, obtiene la lista de posibles fechas de reserva y muestra si está reservada o no de acuerdo con las reglas que describió. Puede resultarle útil como vista de calendario o algo parecido.

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
    )