缺口和岛屿

Question

我有一个包含StartDate和EndDate的表。我想得到丢失的日期和脚本我已经做到了。但例如

LocationID开始日期结束日期

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

我得到的输出是

开始日期结束日期

2020-01-06 2020-01-09

2020-01-16 2020-01-31

但我想要的输出

开始日期结束日期

2020-01-03 2020-01-04

2020-01-05 2020-01-10

2020-01-15 2020-01-31

我可以做什么改变？

可以从晚上到早上预订位置。所以从开始约会的夜晚到结束约会的早晨。因此，除非已预订，否则该地点将在EndDate之夜免费。此外，地点将在开始日期上午免费。

1月1日至1月3日已预订，即从第一晚至第三早。下一次预订只在第4晚。因此，该地点从第三晚到第四早上也是免费的。因此，我需要结果在一行中有3日作为开始日期，1月4日作为结束日期。希望这是清楚的。

感谢

Answer 1

我不会撒谎，我可能有点过于复杂了，但这是我一直走的方向，所以我坚持到底。然而，我确实用Tally代替了你的rCTE，对于更大的布景来说，它应该(明显(更具性能。您可能也需要在PlaceID上添加PARTITION BY和GROUP BY子句，但由于示例基于1个位置，因此我尚未实现这一点。

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 N AS(
SELECT N
FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
Tally AS(
SELECT TOP(DATEDIFF(DAY,@StartDateOfInterest,@EndDateOfInterest)+1)
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS I
FROM N N1, N N2, N N3), --1000 days
Dates AS(
SELECT DATEADD(DAY, I, @StartDateOfInterest) AS [Date]
FROM Tally),
Gaps AS(
SELECT D.[Date],
CASE LAG(DATEADD(DAY,1,D.[Date]),1,D.[Date]) OVER (ORDER BY D.[Date]) WHEN D.Date THEN 1 ELSE 0 END AS [Check]
FROM Dates D
LEFT JOIN @t t ON D.[Date] >= t.StartDate
AND D.[Date] < t.EndDate
WHERE t.PlaceID IS NULL),
Grps AS(
SELECT [Date],
COUNT(CASE [Check] WHEN 0 THEN 1 END) OVER (ORDER BY [Date] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Grp
FROM Gaps)
SELECT MIN([Date]) AS StartDate,
MAX(CASE [Date] WHEN @EndDateOfInterest THEN [Date] ELSE DATEADD(DAY,1,[Date]) END) AS EndDate
FROM Grps
GROUP BY Grp;