寻找补充日期范围？

Question

我有两个表，其中都有 StartDate 和 EndDate 列。

我试图返回一个结果集，其中包含一个表 (TableA) 中的所有日期范围，以及另一个表 (TableB) 中的所有补充日期范围。

CREATE TABLE [dbo].[TableA](
    [ID] [int] NOT NULL,
    [StartDate] [datetime] NOT NULL,
    [EndDate] [datetime] NOT NULL
)

CREATE TABLE [dbo].[TableB](
    [ID] [int] NOT NULL,
    [StartDate] [datetime] NOT NULL,
    [EndDate] [datetime] NOT NULL
)

INSERT INTO TableA (ID, StartDate, EndDate) VALUES(1, '4/1/2009', '8/1/2009')
INSERT INTO TableA (ID, StartDate, EndDate) VALUES(1, '10/1/2009', '12/1/2009')
INSERT INTO TableB (ID, StartDate, EndDate) VALUES(1, '1/1/2009', '2/1/2010')

INSERT INTO TableA (ID, StartDate, EndDate) VALUES(2, '4/1/2009', '8/1/2009')
INSERT INTO TableB (ID, StartDate, EndDate) VALUES(2, '1/1/2009', '5/1/2009')
INSERT INTO TableB (ID, StartDate, EndDate) VALUES(2, '7/1/2009', '12/1/2009')

三个数据集的预期结果集应该是：

(ID = 1)
1/1/2009 - 4/1/2009 (from TableB)
4/1/2009 - 8/1/2009 (from TableA)
8/1/2009 - 10/1/2009 (from TableB)
10/1/2009 - 12/1/2009 (from TableA)
12/1/2009 - 2/1/2010 (from TableB)

(ID = 2)
1/1/2009 - 4/1/2009 (from TableB)
4/1/2009 - 8/1/2009 (from TableA)
8/1/2009 - 12/1/2009 (from TableB)

日期范围不能保证是连续的，并且我无法对它们在表之间如何重叠做出任何假设...在每个表中，可以假设它们不重叠。

我在思考如何将 TableB 中的单个日期范围拆分为多个部分以在 SQL 中找到其中的所有补充“区域”时遇到问题。

有人有什么建议吗？

Answer 1

如果您将其创建为视图，我认为它可以满足您的要求。它使用 CTE，SQL Server 2005 应该支持它，但更早的版本不支持。

WITH Timestamps AS (
    SELECT Id, StartDate AS Date FROM TableA
    UNION
    SELECT Id, EndDate AS Date FROM TableA
    UNION
    SELECT Id, StartDate AS Date FROM TableB
    UNION
    SELECT Id, EndDate AS Date FROM TableB
), Timestamps2 AS (
    SELECT ROW_NUMBER() OVER (ORDER BY Id, Date) AS RowNumber, * FROM Timestamps
), Timestamps3 AS (
    SELECT T1.ID, T1.Date AS StartDate, T2.Date AS EndDate
    FROM Timestamps2 AS T1 JOIN Timestamps2 AS T2
    ON T1.RowNumber + 1 = T2.RowNumber AND T1.ID = T2.ID
), IntervalsFromB AS (
    SELECT T.ID, T.StartDate, T.EndDate FROM Timestamps3 AS T
    LEFT JOIN TableA AS A
    ON T.StartDate >= A.StartDate AND T.EndDate <= A.EndDate
    WHERE A.StartDate IS NULL)
SELECT * FROM TableA
UNION ALL
SELECT * FROM IntervalsFromB

完整输出（按 Id、StartDate 排序以提高可读性）：

Id  StartDate               EndDate
1   2009-01-01 00:00:00.000 2009-04-01 00:00:00.000
1   2009-04-01 00:00:00.000 2009-08-01 00:00:00.000
1   2009-08-01 00:00:00.000 2009-10-01 00:00:00.000
1   2009-10-01 00:00:00.000 2009-12-01 00:00:00.000
1   2009-12-01 00:00:00.000 2010-02-01 00:00:00.000
2   2009-01-01 00:00:00.000 2009-04-01 00:00:00.000
2   2009-04-01 00:00:00.000 2009-08-01 00:00:00.000
2   2009-08-01 00:00:00.000 2009-12-01 00:00:00.000

对我来说实现这个非常复杂，所以我想知道是否有人可以看到更简单的方法。我可能错过了一些让这变得更简单的技巧。如果是这样，请告诉我！此外，如果您有很多行，您几乎肯定需要在表上添加一些索引才能使其正常运行。可能还有其他一些优化 - 我没有尝试过尽可能快的性能，只是为了获得正确的结果。