격차와 섬

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 to EndDate Morning. 따라서 위치는 예약되지 않는 한 EndDate 밤에 무료입니다. 또한 위치는 Startdate Morning에 무료입니다.

1 월 1 일부터 1 월 3 일까지 예약됩니다. 즉, 1 일 밤부터 3 일 아침까지입니다. 다음 예약은 4 일 밤에만 가능합니다. 따라서 위치는 3 일 밤부터 4 일 아침까지 무료입니다. 따라서 한 행에 3rd를 시작 날짜로, 4thJan을 enddate로 지정하는 결과가 필요합니다. 이것이 분명하기를 바랍니다.

Answer 1

당신은 정말로 그것을 위해 cte가 필요하지 않습니다

Window Function 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 | Nextdate  
------ : | : --------- | : ---------
      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
    )