如何在SQL的TimeShift_Dim表中新增符合轮班规则的TeamID列

核心逻辑梳理 #

• 基准规则：2023-07-31（周一）作为起始点，当日A班对应TeamID=1，B班=2，C班=3；之后每周轮换规则变为C=1、A=2、B=3，每2周完成一次循环。
• 排除范围：仅A/B/C班需要赋值TeamID，D班、周六夜班、周日所有班次不参与TeamID赋值。
• 轮班周期：每2周为一个完整轮换周期（第1周：A1/B2/C3；第2周：C1/A2/B3；第3周回到A1/B2/C3，以此类推）。

具体SQL实现 #

步骤1：验证逻辑（先查再更，避免误操作） #

先通过SELECT语句验证TeamID的计算结果是否符合预期：

SELECT 
    ShiftDate,
    ShiftId,
    -- 计算目标日期与基准日的周数差（以周一为周起始）
    DATEDIFF(week, '2023-07-31', ShiftDate) AS week_diff,
    -- 生成TeamID值
    CASE
        -- 排除不需要赋值的情况
        WHEN ShiftId = 'D' 
            OR (DATEPART(weekday, ShiftDate) = 7 AND ShiftId LIKE '%夜%') -- 周六夜班
            OR DATEPART(weekday, ShiftDate) = 1 -- 周日所有班次
            THEN NULL
        -- 偶数周（含基准周）：A=1, B=2, C=3
        WHEN DATEDIFF(week, '2023-07-31', ShiftDate) % 2 = 0 THEN
            CASE ShiftId
                WHEN 'A' THEN 1
                WHEN 'B' THEN 2
                WHEN 'C' THEN 3
            END
        -- 奇数周：C=1, A=2, B=3
        ELSE
            CASE ShiftId
                WHEN 'C' THEN 1
                WHEN 'A' THEN 2
                WHEN 'B' THEN 3
            END
        END AS TeamID
FROM TimeShift_Dim
WHERE ShiftId IN ('A', 'B', 'C');

步骤2：补全历史数据的TeamID #

确认结果正确后，执行UPDATE语句补全历史数据：

UPDATE TimeShift_Dim
SET TeamID = CASE
    WHEN ShiftId = 'D' 
        OR (DATEPART(weekday, ShiftDate) = 7 AND ShiftId LIKE '%夜%')
        OR DATEPART(weekday, ShiftDate) = 1
        THEN NULL
    WHEN DATEDIFF(week, '2023-07-31', ShiftDate) % 2 = 0 THEN
        CASE ShiftId
            WHEN 'A' THEN 1
            WHEN 'B' THEN 2
            WHEN 'C' THEN 3
        END
    ELSE
        CASE ShiftId
            WHEN 'C' THEN 1
            WHEN 'A' THEN 2
            WHEN 'B' THEN 3
        END
    END
WHERE ShiftId IN ('A', 'B', 'C')
  AND NOT (DATEPART(weekday, ShiftDate) = 7 AND ShiftId LIKE '%夜%')
  AND DATEPART(weekday, ShiftDate) != 1;

步骤3：生成未来数据时自动赋值TeamID #

如果需要生成未来日期的班次数据，可以在插入语句中直接嵌入TeamID的计算逻辑，以下是用递归CTE生成未来1年班次数据的示例：

WITH future_dates AS (
    -- 起始日期设为当前日期，可按需调整
    SELECT CAST(GETDATE() AS DATE) AS ShiftDate
    UNION ALL
    SELECT DATEADD(day, 1, ShiftDate)
    FROM future_dates
    WHERE ShiftDate <= DATEADD(year, 1, GETDATE())
),
shift_list AS (
    SELECT 'A' AS ShiftId UNION ALL
    SELECT 'B' UNION ALL
    SELECT 'C' UNION ALL
    SELECT 'D'
)
INSERT INTO TimeShift_Dim (ShiftDate, ShiftId, TeamID)
SELECT 
    fd.ShiftDate,
    sl.ShiftId,
    CASE
        WHEN sl.ShiftId = 'D' 
            OR (DATEPART(weekday, fd.ShiftDate) = 7 AND sl.ShiftId LIKE '%夜%')
            OR DATEPART(weekday, fd.ShiftDate) = 1
            THEN NULL
        WHEN DATEDIFF(week, '2023-07-31', fd.ShiftDate) % 2 = 0 THEN
            CASE sl.ShiftId
                WHEN 'A' THEN 1
                WHEN 'B' THEN 2
                WHEN 'C' THEN 3
            END
        ELSE
            CASE sl.ShiftId
                WHEN 'C' THEN 1
                WHEN 'A' THEN 2
                WHEN 'B' THEN 3
            END
        END AS TeamID
FROM future_dates fd
CROSS JOIN shift_list sl
-- 过滤A/B/C班的周六夜班和周日班次
WHERE NOT (sl.ShiftId IN ('A','B','C') 
           AND (DATEPART(weekday, fd.ShiftDate) = 1 
                OR (DATEPART(weekday, fd.ShiftDate) =7 AND sl.ShiftId LIKE '%夜%')))
OPTION (MAXRECURSION 366); -- 递归次数匹配1年的天数

注意事项 #

• 周起始适配：如果你的SQL环境中DATEPART(weekday)的取值规则不同（比如周一为1，周日为7），需要对应调整DATEPART(weekday, ShiftDate) = 1（周日）和=7（周六）的判断条件。
• 班次标识适配：如果你的夜班不是用“夜”字标识（比如A白=A_Day，A夜=A_Night），需要修改ShiftId LIKE '%夜%'的匹配规则，替换成实际的夜班标识逻辑。
• 数据安全：执行UPDATE前务必通过SELECT验证结果，或者先备份表数据，避免误更新。

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