sql - 寻找补充日期范围？

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

如果您将其创建为 View ，我认为它可以满足您的需求。它使用 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

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