SQL Server：拆分夜班员工工时区间并关联休息表

表结构与测试数据 #

工作时间表（workingSchedule） #

CREATE TABLE workingSchedule 
(
    [workingDate] DATETIME NULL,
    [openTime]    TIME(7) NULL,
    [closeTime]   TIME(7) NULL
);

INSERT INTO workingSchedule (workingDate, openTime, closeTime)
VALUES ('10/1/2015','23:00','00:00'),
       ('10/2/2015','00:00','07:00'),
       ('10/2/2015','23:00','00:00'),
       ('10/3/2015','00:00','07:00');

休息表（breakTable） #

CREATE TABLE breakTable 
(
    [breakDate] DATETIME NULL,
    [breakStart] TIME NULL,
    [breakEnd] TIME NULL
);

INSERT INTO breakTable (breakDate, breakStart, breakEnd)
VALUES ('10/1/2015','23:30','23:45'),
       ('10/2/2015','00:00','00:30'),
       ('10/2/2015','03:00','03:15'),
       ('10/2/2015','23:00','07:00'); --- 此条为8小时假期记录

需求说明 #

需要结合休息表，将工作时间表中的工时区间拆分为多行，最终期望输出如下：

Date          Start    End  
----------------------------
10/1/2015     23:00    23:30  
10/1/2015     23:45    00:00  
10/1/2015     00:30    03:00  
10/2/2015     03:15    07:00  
10/2/2015     00:00    08:00   

注：最后一条记录对应8小时假期场景。

当前问题 #

现有代码仅适用于白班员工，无法正确关联夜班员工的休息时间与对应工时区间，代码如下：

SELECT  
    workingDate AS [date], openTime AS [Start], 
    COALESCE(breakStart, closeTime) AS [End]
FROM    
    workingSchedule
LEFT JOIN 
    (SELECT  
         breakDate, breakStart, breakEnd, 
         ROW_NUMBER() OVER (PARTITION BY breakDate ORDER BY breakStart) AS ROWNUM
     FROM    
         breakTable) AS firstBreak ON workingSchedule.workingDate = firstBreak.breakDate 
                                   AND firstBreak.ROWNUM = 1

UNION

SELECT  
    breakStart.breakDate, breakStart.breakEnd,   
    COALESCE(breakEnd.breakStart, endTime.closeTime)
FROM    
    (SELECT  
         breakDate, breakStart, breakEnd, 
         ROW_NUMBER() OVER (PARTITION BY breakDate ORDER BY breakStart) AS ROWNUM
     FROM    
         breakTable) AS breakStart
LEFT JOIN 
    (SELECT  
         breakDate, breakStart, breakEnd, 
         ROW_NUMBER() OVER (PARTITION BY breakDate ORDER BY breakStart) AS ROWNUM
     FROM    
         breakTable) AS breakEnd ON breakStart.breakDate = breakEnd.breakDate 
                                 AND breakStart.ROWNUM = breakEnd.ROWNUM - 1
LEFT JOIN 
    (SELECT  
         workingDate, closeTime
     FROM    
         workingSchedule) AS endTime ON breakStart.breakDate = endTime.workingDate

解决方案 #

要解决夜班时间跨天的关联问题，核心是将时间转换为带日期的完整datetime类型，避免单独比较time类型导致的逻辑错误。以下是修正后的SQL：

WITH WorkingPeriods AS (
    -- 将工作时间转换为完整的datetime，处理跨天情况
    SELECT 
        workingDate,
        CASE 
            WHEN openTime <= closeTime THEN DATEADD(day, 0, CAST(workingDate AS DATE)) + CAST(openTime AS DATETIME)
            ELSE DATEADD(day, 0, CAST(workingDate AS DATE)) + CAST(openTime AS DATETIME)
        END AS workStart,
        CASE 
            WHEN openTime <= closeTime THEN DATEADD(day, 0, CAST(workingDate AS DATE)) + CAST(closeTime AS DATETIME)
            ELSE DATEADD(day, 1, CAST(workingDate AS DATE)) + CAST(closeTime AS DATETIME)
        END AS workEnd
    FROM workingSchedule
),
Breaks AS (
    -- 将休息时间转换为完整的datetime，处理跨天假期
    SELECT 
        breakDate,
        DATEADD(day, 0, CAST(breakDate AS DATE)) + CAST(breakStart AS DATETIME) AS breakStart,
        CASE 
            WHEN breakStart <= breakEnd THEN DATEADD(day, 0, CAST(breakDate AS DATE)) + CAST(breakEnd AS DATETIME)
            ELSE DATEADD(day, 1, CAST(breakDate AS DATE)) + CAST(breakEnd AS DATETIME)
        END AS breakEnd
    FROM breakTable
),
AllEvents AS (
    -- 合并工作区间的开始、结束，以及休息的开始、结束事件
    SELECT workStart AS eventTime, 'START' AS eventType, workStart, workEnd FROM WorkingPeriods
    UNION ALL
    SELECT workEnd AS eventTime, 'END' AS eventType, workStart, workEnd FROM WorkingPeriods
    UNION ALL
    SELECT breakStart AS eventTime, 'BREAK_START' AS eventType, NULL, NULL FROM Breaks
    UNION ALL
    SELECT breakEnd AS eventTime, 'BREAK_END' AS eventType, NULL, NULL FROM Breaks
),
OrderedEvents AS (
    -- 按时间排序事件，同时关联所属的工作区间
    SELECT 
        eventTime,
        eventType,
        -- 找到当前事件所属的工作区间（事件时间在工作区间内）
        MAX(workStart) OVER (ORDER BY eventTime) AS currentWorkStart,
        MAX(workEnd) OVER (ORDER BY eventTime) AS currentWorkEnd
    FROM AllEvents
    WHERE eventTime BETWEEN (SELECT MIN(workStart) FROM WorkingPeriods) AND (SELECT MAX(workEnd) FROM WorkingPeriods)
    ORDER BY eventTime
),
WorkIntervals AS (
    -- 生成有效工作区间：排除休息时段
    SELECT 
        CASE 
            WHEN DATEPART(day, prevEventTime) = DATEPART(day, currentWorkStart) THEN CAST(currentWorkStart AS DATE)
            ELSE CAST(prevEventTime AS DATE)
        END AS [Date],
        CAST(prevEventTime AS TIME) AS [Start],
        CAST(eventTime AS TIME) AS [End]
    FROM (
        SELECT 
            eventTime,
            LAG(eventTime) OVER (ORDER BY eventTime) AS prevEventTime,
            currentWorkStart,
            currentWorkEnd,
            -- 标记连续的工作时段（非休息状态）
            SUM(CASE 
                    WHEN eventType = 'START' THEN 1
                    WHEN eventType = 'END' THEN -1
                    WHEN eventType = 'BREAK_START' THEN -1
                    WHEN eventType = 'BREAK_END' THEN 1
                END) OVER (ORDER BY eventTime) AS workStatus
        FROM OrderedEvents
    ) t
    WHERE workStatus = 1 -- 仅保留工作状态为活跃的区间
      AND prevEventTime IS NOT NULL
      AND prevEventTime < eventTime -- 排除零时长区间
)
SELECT 
    CONVERT(VARCHAR(10), [Date], 101) AS [Date],
    [Start],
    [End]
FROM WorkIntervals
ORDER BY [Date], [Start];

方案说明 #

1. 时间转换：将单独的日期和时间字段合并为完整的datetime，解决跨天时间比较的逻辑错误。
2. 事件驱动：把工作开始/结束、休息开始/结束都作为事件，按时间排序后，通过状态标记判断当前是否处于工作时段。
3. 区间生成：基于事件序列，提取连续的工作状态区间，最终转换回日期+时间的格式输出。

内容的提问来源于stack exchange，提问作者Shamika Aj