如何基于住院起止时间生成12小时护士班次唯一行（MS SQL/T-SQL）

问题分析 #

需要将单条住院记录，按**8:00-20:00（白班）、20:00-次日8:00（夜班）**的12小时班次拆分，生成包含班次起止时间、班次编号的多行记录，仅保留与住院时间有交集的班次。

完整解决方案（递归CTE版本） #

递归CTE适合灵活处理任意长度的住院周期，无需预先定义固定的数字范围：

-- 1. 创建示例表（可替换为你的实际表）
CREATE TABLE Admissions (
    RowKey INT,
    Patient VARCHAR(50),
    AdmissionDateTime DATETIME,
    DischargeDateTime DATETIME
);

INSERT INTO Admissions VALUES
(222, 'Joe Blogs', '2024-02-21 10:06:00', '2024-02-23 23:18:00');

-- 2. 递归生成班次记录
WITH ShiftCTE AS (
    -- 初始化：获取每个患者入院所在的第一个班次
    SELECT 
        a.RowKey,
        a.Patient,
        a.AdmissionDateTime,
        a.DischargeDateTime,
        -- 计算第一个班次的开始时间
        CASE 
            WHEN DATEPART(HOUR, a.AdmissionDateTime) BETWEEN 8 AND 19 
                THEN CAST(CAST(a.AdmissionDateTime AS DATE) AS DATETIME) + '08:00:00'
            ELSE CAST(CAST(a.AdmissionDateTime AS DATE) AS DATETIME) - '04:00:00' -- 前一天20:00
        END AS ShiftStartDateTime,
        -- 计算第一个班次的结束时间
        CASE 
            WHEN DATEPART(HOUR, a.AdmissionDateTime) BETWEEN 8 AND 19 
                THEN CAST(CAST(a.AdmissionDateTime AS DATE) AS DATETIME) + '19:59:59'
            ELSE CAST(CAST(a.AdmissionDateTime AS DATE) AS DATETIME) + '07:59:59'
        END AS ShiftEndDateTime
    FROM Admissions a

    UNION ALL

    -- 递归生成后续班次
    SELECT 
        s.RowKey,
        s.Patient,
        s.AdmissionDateTime,
        s.DischargeDateTime,
        DATEADD(SECOND, 1, s.ShiftEndDateTime) AS ShiftStartDateTime,
        -- 计算下一班次的结束时间
        CASE 
            WHEN DATEPART(HOUR, s.ShiftEndDateTime) = 19 
                THEN DATEADD(DAY, 1, CAST(CAST(s.ShiftEndDateTime AS DATE) AS DATETIME) + '07:59:59')
            ELSE CAST(CAST(DATEADD(DAY, 1, s.ShiftEndDateTime) AS DATE) AS DATETIME) + '19:59:59'
        END AS ShiftEndDateTime
    FROM ShiftCTE s
    -- 终止条件：下一班次开始时间早于出院时间
    WHERE DATEADD(SECOND, 1, s.ShiftEndDateTime) < s.DischargeDateTime
)
-- 最终输出：过滤有效班次并生成编号
SELECT 
    ROW_NUMBER() OVER (ORDER BY s.RowKey, s.ShiftStartDateTime) AS NewRowKey,
    s.Patient,
    s.AdmissionDateTime,
    s.DischargeDateTime,
    s.ShiftStartDateTime,
    s.ShiftEndDateTime,
    ROW_NUMBER() OVER (PARTITION BY s.RowKey ORDER BY s.ShiftStartDateTime) AS ShiftNumber
FROM ShiftCTE s
-- 仅保留与住院时间有交集的班次
WHERE s.ShiftStartDateTime < s.DischargeDateTime
  AND s.ShiftEndDateTime > s.AdmissionDateTime
ORDER BY s.RowKey, s.ShiftStartDateTime
-- 若住院周期超过100天，解除递归深度限制：OPTION (MAXRECURSION 0);

替代方案（数字表版本） #

适合住院周期较长的场景，避免递归深度限制：

WITH Numbers AS (
    -- 生成足够多的数字（此处生成2000个，覆盖1000天的班次）
    SELECT TOP 2000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS N
    FROM master.dbo.spt_values
),
AllShifts AS (
    -- 生成所有可能的班次开始时间
    SELECT 
        a.RowKey,
        a.Patient,
        a.AdmissionDateTime,
        a.DischargeDateTime,
        DATEADD(HOUR, 12 * n.N, 
            CASE 
                WHEN DATEPART(HOUR, a.AdmissionDateTime) BETWEEN 8 AND 19 
                    THEN CAST(CAST(a.AdmissionDateTime AS DATE) AS DATETIME) + '08:00:00'
                ELSE CAST(CAST(a.AdmissionDateTime AS DATE) AS DATETIME) - '04:00:00'
            END) AS ShiftStartDateTime
    FROM Admissions a
    CROSS JOIN Numbers n
),
ShiftDetails AS (
    -- 计算每个班次的结束时间
    SELECT 
        *,
        CASE 
            WHEN DATEPART(HOUR, ShiftStartDateTime) = 8 
                THEN DATEADD(SECOND, 43199, ShiftStartDateTime) -- 8:00 + 11h59m59s = 19:59:59
            ELSE DATEADD(SECOND, 43199, ShiftStartDateTime) -- 20:00 + 11h59m59s = 次日7:59:59
        END AS ShiftEndDateTime
    FROM AllShifts
)
-- 输出结果
SELECT 
    ROW_NUMBER() OVER (ORDER BY d.RowKey, d.ShiftStartDateTime) AS NewRowKey,
    d.Patient,
    d.AdmissionDateTime,
    d.DischargeDateTime,
    d.ShiftStartDateTime,
    d.ShiftEndDateTime,
    ROW_NUMBER() OVER (PARTITION BY d.RowKey ORDER BY d.ShiftStartDateTime) AS ShiftNumber
FROM ShiftDetails d
WHERE d.ShiftStartDateTime < d.DischargeDateTime
  AND d.ShiftEndDateTime > d.AdmissionDateTime
ORDER BY d.RowKey, d.ShiftStartDateTime;

关键逻辑说明 #

1. 班次起止计算：
   • 白班：当日8:00至19:59:59
   • 夜班：当日20:00至次日7:59:59
2. 有效班次过滤：仅保留班次开始时间 < 出院时间且班次结束时间 > 入院时间的记录，避免生成完全在住院周期外的班次。
3. 编号生成：
   • NewRowKey：全局唯一的新行号
   • ShiftNumber：按患者分组的班次顺序号（从1开始）

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