SQL Server中移除计划时段与不可用时段的重叠区间

我在SQL Server数据库中有两个表：

• PlannedPeriods：存储未来事件的时段信息
• UnavailabledPeriods：存储不可用的时段信息

表结构及数据示例如下：

CREATE TABLE PlannedPeriods(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [DateFrom] [datetime] NOT NULL,
    [DateTo] [datetime] NOT NULL,
    [Event] [nvarchar](max) NULL
)
INSERT INTO PlannedPeriods (DateFrom, DateTo, Event)
VALUES
    ('2022-08-29 12:00:00', '2022-08-30 04:00:00', 'Event 1'),
    ('2022-08-30 12:00:00', '2022-08-30 14:00:00', 'Event 2'),
    ('2022-08-31 18:30:00', '2022-08-31 22:30:00', 'Event 3')


CREATE TABLE UnavailabledPeriods(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [DateFrom] [datetime] NOT NULL,
    [DateTo] [datetime] NOT NULL
)
INSERT INTO UnavailabledPeriods (DateFrom, DateTo)
VALUES
    ('2022-08-30 01:00:00', '2022-08-30 03:00:00'),
    ('2022-08-31 15:00:00', '2022-08-31 19:00:00')

需要从计划时段中移除所有不可用时段，最终得到如下结果：

DateFrom             | DateTo
2022-08-29 12:00:00  | 2022-08-30 01:00:00
2022-08-30 03:00:00  | 2022-08-30 04:00:00 
2022-08-30 12:00:00  | 2022-08-30 14:00:00
2022-08-31 19:00:00  | 2022-08-31 22:30:00

解决方案 #

可以通过CTE（公共表表达式）提取关键时间点，再筛选出有效时段，SQL代码如下：

WITH AllTimePoints AS (
    -- 收集所有计划时段和不可用时段的起始、结束时间点
    SELECT DateFrom AS TimePoint FROM PlannedPeriods
    UNION
    SELECT DateTo AS TimePoint FROM PlannedPeriods
    UNION
    SELECT DateFrom AS TimePoint FROM UnavailabledPeriods
    UNION
    SELECT DateTo AS TimePoint FROM UnavailabledPeriods
),
OrderedTimePoints AS (
    -- 对时间点排序，并获取每个点的下一个时间点
    SELECT 
        TimePoint,
        LEAD(TimePoint) OVER (ORDER BY TimePoint) AS NextTimePoint
    FROM AllTimePoints
),
ValidPeriods AS (
    -- 筛选属于计划时段且不与不可用时段重叠的区间
    SELECT 
        otp.TimePoint AS DateFrom,
        otp.NextTimePoint AS DateTo
    FROM OrderedTimePoints otp
    JOIN PlannedPeriods pp 
        ON otp.TimePoint >= pp.DateFrom 
        AND otp.NextTimePoint <= pp.DateTo
    LEFT JOIN UnavailabledPeriods up 
        ON (otp.TimePoint < up.DateTo AND otp.NextTimePoint > up.DateFrom)
    WHERE up.Id IS NULL
      AND otp.NextTimePoint IS NOT NULL
)
SELECT DateFrom, DateTo FROM ValidPeriods ORDER BY DateFrom;

逻辑说明 #

1. AllTimePoints：提取所有计划时段和不可用时段的时间节点，这些节点是分割时段的关键。
2. OrderedTimePoints：对时间节点排序，用LEAD函数生成连续的小时间区间。
3. ValidPeriods：筛选出完全落在计划时段内，且不与任何不可用时段重叠的小区间，这些就是扣除不可用时段后的有效时段。

内容的提问来源于stack exchange，提问作者J. P.