在TSQL中拆分日期范围为多个员工专属有效时间段

需求说明 #

需要在TSQL中处理同一caseId下的重叠日期范围，为每个员工划分出无重叠的有效时间段。优先采用SQL视图实现，存储过程也可接受。此前尝试用CTE实现时，无法生成完整结果（比如测试数据中caseId=1的第三行），现寻求可行解决方案。

输入测试数据 #

drop table if exists #ranges
create table #ranges (caseId int not null, startDate date  not null, endDate date not null, Employee VARCHAR(50))

insert #ranges select 1, '2023-01-01', '2023-12-31','ABc'
insert #ranges select 1, '2023-01-15', '2023-05-01', 'CBa'

insert #ranges select 2, '2023-01-01', '2023-05-01', 'ABc'
insert #ranges select 2, '2023-01-15', '2023-03-01', 'DEf'
insert #ranges select 2, '2023-02-01', '2023-12-31', 'GHi'

期望输出结果 #

drop table if exists #result
create table #result (caseId int not null, startDate date  not null, endDate date not null, Employee VARCHAR(50))
insert #result select 1, '2023-01-01', '2023-01-14','ABc'
insert #result select 1, '2023-01-15', '2023-05-01', 'CBa'
insert #result select 1, '2023-05-02', '2023-12-31','ABc'

insert #result select 2, '2023-01-01', '2023-01-14','ABc'
insert #result select 2, '2023-01-15', '2023-03-01', 'DEf'
insert #result select 2, '2023-03-02', '2023-05-01','ABc'
insert #result select 2, '2023-05-02', '2023-12-31','GHi'

解决方案 #

以下是基于CTE的实现方案，核心思路是：先定位每个员工原始时间段中被其他员工覆盖的重叠区间，再将原始时间段拆分为未被覆盖的有效子区间：

WITH Overlaps AS (
    -- 找出当前员工时间段与其他员工的重叠部分
    SELECT 
        r1.caseId,
        r1.Employee,
        CASE WHEN r1.startDate <= r2.startDate THEN r2.startDate ELSE r1.startDate END AS OverlapStart,
        CASE WHEN r1.endDate >= r2.endDate THEN r2.endDate ELSE r1.endDate END AS OverlapEnd
    FROM #ranges r1
    JOIN #ranges r2 
        ON r1.caseId = r2.caseId 
        AND r1.Employee != r2.Employee
        AND r1.startDate <= r2.endDate 
        AND r1.endDate >= r2.startDate
),
MergedOverlaps AS (
    -- 合并重叠的排除区间，避免重复拆分
    SELECT 
        caseId,
        Employee,
        OverlapStart,
        OverlapEnd
    FROM Overlaps o1
    WHERE NOT EXISTS (
        SELECT 1 
        FROM Overlaps o2
        WHERE o2.caseId = o1.caseId 
          AND o2.Employee = o1.Employee
          AND o2.OverlapStart <= o1.OverlapStart 
          AND o2.OverlapEnd >= o1.OverlapEnd
          AND (o2.OverlapStart < o1.OverlapStart OR o2.OverlapEnd > o1.OverlapEnd)
    )
    UNION ALL
    SELECT 
        mo.caseId,
        mo.Employee,
        mo.OverlapStart,
        o.OverlapEnd
    FROM MergedOverlaps mo
    JOIN Overlaps o 
        ON mo.caseId = o.caseId 
        AND mo.Employee = o.Employee
        AND o.OverlapStart <= DATEADD(DAY, 1, mo.OverlapEnd) 
        AND o.OverlapEnd > mo.OverlapEnd
        AND NOT EXISTS (
            SELECT 1 
            FROM MergedOverlaps mo2
            WHERE mo2.caseId = mo.caseId 
              AND mo2.Employee = mo.Employee
              AND mo2.OverlapStart <= mo.OverlapStart 
              AND mo2.OverlapEnd >= o.OverlapEnd
        )
),
DistinctMergedOverlaps AS (
    -- 去重合并后的排除区间
    SELECT DISTINCT caseId, Employee, OverlapStart, OverlapEnd 
    FROM MergedOverlaps
),
SplitPoints AS (
    -- 生成所有需要拆分的日期点：原始区间的起止、重叠区间的起止
    SELECT caseId, Employee, startDate AS Point FROM #ranges
    UNION
    SELECT caseId, Employee, DATEADD(DAY, 1, endDate) AS Point FROM #ranges
    UNION
    SELECT caseId, Employee, OverlapStart AS Point FROM DistinctMergedOverlaps
    UNION
    SELECT caseId, Employee, DATEADD(DAY, 1, OverlapEnd) AS Point FROM DistinctMergedOverlaps
),
SplitRanges AS (
    -- 基于拆分点生成连续的子时间段
    SELECT 
        caseId,
        Employee,
        Point AS StartDate,
        LEAD(Point) OVER (PARTITION BY caseId, Employee ORDER BY Point) AS NextPoint
    FROM SplitPoints
),
ValidRanges AS (
    -- 筛选出属于原始区间且未被覆盖的有效子时间段
    SELECT 
        sr.caseId,
        sr.StartDate,
        DATEADD(DAY, -1, sr.NextPoint) AS EndDate,
        sr.Employee
    FROM SplitRanges sr
    JOIN #ranges r 
        ON sr.caseId = r.caseId 
        AND sr.Employee = r.Employee
        AND sr.StartDate >= r.startDate 
        AND DATEADD(DAY, -1, sr.NextPoint) <= r.endDate
    WHERE sr.NextPoint IS NOT NULL
    AND NOT EXISTS (
        SELECT 1 
        FROM DistinctMergedOverlaps mo
        WHERE mo.caseId = sr.caseId 
          AND mo.Employee = sr.Employee
          AND mo.OverlapStart <= sr.StartDate 
          AND mo.OverlapEnd >= DATEADD(DAY, -1, sr.NextPoint)
    )
)
-- 输出最终结果
SELECT * FROM ValidRanges ORDER BY caseId, StartDate;

如果需要封装为视图，只需将临时表#ranges替换为正式表（比如dbo.ranges），将上述逻辑整合到视图中即可：

CREATE VIEW vw_EmployeeValidRanges AS
WITH Overlaps AS (
    SELECT 
        r1.caseId,
        r1.Employee,
        CASE WHEN r1.startDate <= r2.startDate THEN r2.startDate ELSE r1.startDate END AS OverlapStart,
        CASE WHEN r1.endDate >= r2.endDate THEN r2.endDate ELSE r1.endDate END AS OverlapEnd
    FROM dbo.ranges r1
    JOIN dbo.ranges r2 
        ON r1.caseId = r2.caseId 
        AND r1.Employee != r2.Employee
        AND r1.startDate <= r2.endDate 
        AND r1.endDate >= r2.startDate
),
MergedOverlaps AS (
    SELECT 
        caseId,
        Employee,
        OverlapStart,
        OverlapEnd
    FROM Overlaps o1
    WHERE NOT EXISTS (
        SELECT 1 
        FROM Overlaps o2
        WHERE o2.caseId = o1.caseId 
          AND o2.Employee = o1.Employee
          AND o2.OverlapStart <= o1.OverlapStart 
          AND o2.OverlapEnd >= o1.OverlapEnd
          AND (o2.OverlapStart < o1.OverlapStart OR o2.OverlapEnd > o1.OverlapEnd)
    )
    UNION ALL
    SELECT 
        mo.caseId,
        mo.Employee,
        mo.OverlapStart,
        o.OverlapEnd
    FROM MergedOverlaps mo
    JOIN Overlaps o 
        ON mo.caseId = o.caseId 
        AND mo.Employee = o.Employee
        AND o.OverlapStart <= DATEADD(DAY, 1, mo.OverlapEnd) 
        AND o.OverlapEnd > mo.OverlapEnd
        AND NOT EXISTS (
            SELECT 1 
            FROM MergedOverlaps mo2
            WHERE mo2.caseId = mo.caseId 
              AND mo2.Employee = mo.Employee
              AND mo2.OverlapStart <= mo.OverlapStart 
              AND mo2.OverlapEnd >= o.OverlapEnd
        )
),
DistinctMergedOverlaps AS (
    SELECT DISTINCT caseId, Employee, OverlapStart, OverlapEnd 
    FROM MergedOverlaps
),
SplitPoints AS (
    SELECT caseId, Employee, startDate AS Point FROM dbo.ranges
    UNION
    SELECT caseId, Employee, DATEADD(DAY, 1, endDate) AS Point FROM dbo.ranges
    UNION
    SELECT caseId, Employee, OverlapStart AS Point FROM DistinctMergedOverlaps
    UNION
    SELECT caseId, Employee, DATEADD(DAY, 1, OverlapEnd) AS Point FROM DistinctMergedOverlaps
),
SplitRanges AS (
    SELECT 
        caseId,
        Employee,
        Point AS StartDate,
        LEAD(Point) OVER (PARTITION BY caseId, Employee ORDER BY Point) AS NextPoint
    FROM SplitPoints
),
ValidRanges AS (
    SELECT 
        sr.caseId,
        sr.StartDate,
        DATEADD(DAY, -1, sr.NextPoint) AS EndDate,
        sr.Employee
    FROM SplitRanges sr
    JOIN dbo.ranges r 
        ON sr.caseId = r.caseId 
        AND sr.Employee = r.Employee
        AND sr.StartDate >= r.startDate 
        AND DATEADD(DAY, -1, sr.NextPoint) <= r.endDate
    WHERE sr.NextPoint IS NOT NULL
    AND NOT EXISTS (
        SELECT 1 
        FROM DistinctMergedOverlaps mo
        WHERE mo.caseId = sr.caseId 
          AND mo.Employee = sr.Employee
          AND mo.OverlapStart <= sr.StartDate 
          AND mo.OverlapEnd >= DATEADD(DAY, -1, sr.NextPoint)
    )
)
SELECT * FROM ValidRanges;

内容的提问来源于stack exchange，提问作者Dani C.