查找两个日期之间的缺失日期范围

Question

我有一个表，其中包含一些日期范围的数据。当用户选择开始日期和结束日期时，结果集将类似于这两个日期之间的所有日期范围，也类似于这2个日期之间所有缺失的日期范围。
例如：
日期范围表

ID| fromdate  | todate    |
----------------------------
1 | 5-May-21  | 10-May-21 |
2 | 17-May-21 | 25-May-21 |

这是我的主表，我在下面提到了我想要的上表的所有结果集

如果用户选择：2021年5月5日至22021年5月25日
预期结果：

ID| fromdate  | todate    |
----------------------------
1 | 5-May-21  | 10-May-21 |
0 | 11-May-21 | 16-May-21 |
2 | 17-May-21 | 25-May-21 |

如果用户选择：2021年5月6日至2021年5月23日
预期结果：

ID| fromdate  | todate    |
-----------------------------
1 | 6-May-21  | 10-May-21 |
0 | 11-May-21 | 16-May-21 |
2 | 17-May-21 | 23-May-21 |

如果用户选择：2021年5月1日至2022年五月28日
预期结果：

ID| fromdate  | todate    |
----------------------------
1 | 1-May-21  | 4-May-21  |
1 | 5-May-21  | 10-May-21 |
0 | 11-May-21 | 16-May-21 |
2 | 17-May-21 | 25-May-21 |
2 | 26-May-21 | 28-May-21 |

这里有些问题是不相似的，但试图找到：

SQL查找缺少的日期范围

SQL如何编写返回缺少日期范围的查询？

提前谢谢。

Answer 1

注意，我在这里假设您的最终预期结果的预期结果在这里是错误的，因为它与其他2个不匹配。最后一个集合的预期结果中的最后一行和第一行的ID值都不是0，但没有解释为什么会这样做。因此我假设该值应该是CCD_ 3；中间"；。

为此，我使用Tally来获取您需要的日期范围之间的所有日期；Tally限制为1000行，还有3年的时间，但如果需要更多行，可以交叉连接到N。然后，我使用该计数创建一个内联日历表。接下来，我将该日历LEFT JOIN添加到您的数据中，并使用间隙和孤岛方法将值分组。最后，我对这些组进行聚合，得到每个组中的MIN和MAX日期：

USE Sandbox;
GO
CREATE TABLE dbo.YourTable (ID int,
FromDate date,
ToDate date);
INSERT INTO dbo.YourTable
VALUES(1,'20210505','20210510'),
(2,'20210517','20210525');
GO
DECLARE @StartDate date = '20210501',
@EndDate date = '20210528';
WITH N AS(
SELECT N
FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
Tally AS(
SELECT 0 AS I
UNION ALL
SELECT TOP (DATEDIFF(DAY, @StartDate, @EndDate))
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
FROM N N1, N N2, N N3), --1000 days
Dates AS(
SELECT DATEADD(DAY, T.I, @StartDate) AS [Date],
T.I
FROM Tally T),
Grps AS(
SELECT D.[Date],
YT.ID,
D.I - ROW_NUMBER() OVER (PARTITION BY ID ORDER BY D.[Date]) AS Grp
FROM Dates D
LEFT JOIN dbo.YourTable YT ON D.[Date] >= YT.FromDate AND D.[Date] <= YT.ToDate)
SELECT ISNULL(MAX(G.ID),0) AS ID,
MIN(G.[Date]) AS FromDate,
MAX(G.[Date]) AS ToDate
FROM Grps G
GROUP BY G.Grp
ORDER BY FromDate ASC;
GO
DROP TABLE dbo.YourTable;

db<gt；小提琴

Answer 2

您可以使用union all进行以下操作：

-- first get the existing rows
select id,
(case when fromdate < @fromdate then @fromdate else fromdate end) as fromdate,
(case when todate > @todate then @todate else todate end) as to_date
from t
where fromdate < @todate and
todate > @fromdate
union all
-- then the in-between rows
select 0, dateadd(day, 1, todate) as fromdate, next_fromdate as todate
from (select t.*,
dateadd(day, -1, lead(fromdate) over (order by fromdate)) as next_fromdate
from t
) t
where fromdate >= @fromdate and todate <= @todate and
next_todate is not null
union all
-- then the earliest record, if any
select 0, @fromdate, min(fromdate)
from t
where todate > @fromdate
having @fromdate < min(fromdate) 
union all
-- then the final record, if any
select 0, max(todate), @todate
from t
where fromdate < @todate
having @todate > max(todate);

这里有一个db<gt；不停摆弄