Luki i wyspy

Question

Mam tabelę z StartDate i EndDate. Chciałem uzyskać brakującą datę, a skrypt, który mam, to robi. Ale jeśli podano te dane:

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

To zapytanie:

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

Zwraca ten wynik:

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

Ale chciałem wyjść jako:

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

Jakie zmiany mogę wprowadzić?

Edytowano, aby dodać: lokalizację można zarezerwować od nocy do rana. Więc StartDate Night to EndDate rano. Tak więc lokalizacja będzie bezpłatna w nocy EndDate, chyba że zostanie zarezerwowana. Również lokalizacja będzie bezpłatna w Startdate Morning.

1 stycznia do 3 stycznia jest zarezerwowany, czyli od 1 nocy do 3 rano. następna rezerwacja tylko na 4. noc. Więc lokalizacja jest wolna od 3 nocy do 4 rano. Więc potrzebowałbym wyniku, aby mieć datę rozpoczęcia 3 jako datę rozpoczęcia i datę 4 stycznia jako datę końcową w jednym wierszu. Mam nadzieję, że to jest jasne.

Answer 1

Naprawdę nie potrzebujesz do tego cte

Funkcja okna LEAD może zrobić to, czego potrzebujesz

Można to poprawić, jeśli daty się zgadzają

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 | Data zakończenia | Następna data  
------: | : --------- | : ---------
      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 <> skrzypce tutaj

Answer 2

Odpowiedź @nbk zapewni Ci to, czego chcesz, po prostu umieszczam to tutaj, ponieważ może to być dla Ciebie interesujące; chociaż nie jest kompletna. W tej chwili pobiera listę możliwych terminów rezerwacji i pokazuje, czy jest zarezerwowana, czy nie, zgodnie z określonymi przez Ciebie zasadami. Może być przydatny jako widok kalendarza lub coś podobnego.

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
    )