差距和島嶼

我有一張帶有 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 Morning。因此，除非已預訂，否則該位置將在 EndDate 晚上免費。此外，該位置將在 Startdate 早上免費。

1 月 1 日至 1 月 3 日已預訂 - 這意味著從第一晚到第三天早上。下次預訂僅在第 4 晚。所以這個位置從第三晚到第四天早上也是免費的。所以我需要結果將 3rd 作為開始日期，將 4thJan 作為結束日期在一行中。希望這很清楚。

你真的不需要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

地點ID | 結束日期 | 下一個日期 
------: | :--------- | :---------
 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<>在這裡擺弄

引用自：https://dba.stackexchange.com/questions/278192