时间戳

首页 > 解决方案 > 查找两个日期之间缺失的日期范围

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

问题描述

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

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

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

如果用户选择：2021 年 5 月 5 日至 2021 年 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 日至 2021年5 月 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如何编写返回缺失日期范围的查询？

提前致谢。

标签： sqlsql-serverdatedate-rangebetween

解决方案

注意，我在这里假设您的最终预期结果的预期结果是错误的，因为它与其他 2 不匹配。最后一组预期结果中的最后一行和第一行都有一个ID值t 0，但没有给出他们为什么这样做的解释。因此，我假设该值应该0像“中间”中的行。

为此，我使用 Tally 获取所需日期范围之间的所有日期；Tally 限制为 1,000 行，略低于 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<>小提琴

推荐阅读