Lacunas e ilhas

Question

Eu tenho uma tabela com StartDate e EndDate. Queria obter a data que faltava e o script que tenho faz isso. Mas se forem dados estes dados:

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

Retorna esta saída:

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

Mas eu queria a saída como:

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

Que mudanças posso fazer?

Editado para adicionar: Um local pode ser reservado da noite à manhã. Então StartDate Night to EndDate Morning. PORTANTO, o local será gratuito na noite EndDate, a menos que seja reservado. Além disso, a localização será gratuita na manhã de data de início.

De 1 a 3 de janeiro está reservado - isso significa que da 1ª noite à 3ª manhã. próxima reserva é apenas na 4ª noite. Portanto, a localização é gratuita da 3ª à 4ª manhã também. Então, eu precisaria que o resultado tivesse 3 ° ano como data de início e 4 ° janeiro como data final em uma linha. Espero que isso esteja claro.

Answer 1

Você realmente não precisa de um cte para isso

A função de janela LEAD pode fazer o que você precisa

Pode ser refinado se as datas corresponderem

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 | Nextdate  
------: | : --------- | : ---------
      1 | 2020-01-03 | 04/01/2020
      1 | 2020-01-13 | 2014-01-14
      1 | 2020-01-23 | 31/01/2020
      2 | 2020-01-06 | 07-01-2020
      2 | 2020-01-10 | 2020-01-20
      2 | 2020-01-23 | 31/01/2020

db <> fiddle aqui

Answer 2

A resposta de @nbk vai lhe dar o que você quer, estou apenas colocando isso aqui, pois pode ser interessante para você; embora não esteja completo. No momento, ele obtém a lista de datas de reserva possíveis e mostra se ela está reservada ou não de acordo com as regras que você definiu. Pode ser útil como uma visualização de calendário ou algo parecido para você.

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
    )