SQL Azure多列累计加班计算：20小时上限实现问题

针对你的需求，我们可以通过拆分加班类型→计算分段累计→重新聚合的流程来实现，避免出现负数或累计超限的问题。以下是适配SQL Azure 12.0.2000.8的完整代码：

WITH UnpivotedOT AS (
    -- 拆分OT30/OT50为行数据，同时按年周排序生成行号
    SELECT 
        EmpID,
        Grouping,
        YearWeek,
        OT30,
        OT50,
        Remarks,
        Metric,
        Value,
        ROW_NUMBER() OVER (PARTITION BY EmpID, Grouping, Metric ORDER BY YearWeek) AS RowNo
    FROM YourTableName
    CROSS APPLY (
        VALUES ('OT30', OT30), ('OT50', OT50)
    ) AS UnpivotCols(Metric, Value)
),
CalculatedOT AS (
    -- 计算累计时长并确定有效加班时长（不超过20小时上限）
    SELECT 
        *,
        -- 计算到上一行的累计时长，第一行则为0
        COALESCE(SUM(Value) OVER (
            PARTITION BY EmpID, Grouping, Metric 
            ORDER BY YearWeek 
            ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
        ), 0) AS PreviousCumulative,
        -- 计算当前行的有效时长：若之前累计已达20，则当前为0；否则取剩余额度与当前时长的较小值
        CASE
            WHEN COALESCE(SUM(Value) OVER (
                PARTITION BY EmpID, Grouping, Metric 
                ORDER BY YearWeek 
                ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
            ), 0) >= 20 THEN 0
            ELSE CASE
                WHEN (COALESCE(SUM(Value) OVER (
                    PARTITION BY EmpID, Grouping, Metric 
                    ORDER BY YearWeek 
                    ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
                ), 0) + Value) > 20 THEN 20 - COALESCE(SUM(Value) OVER (
                    PARTITION BY EmpID, Grouping, Metric 
                    ORDER BY YearWeek 
                    ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
                ), 0)
                ELSE Value
            END
        END AS EffectiveOT
    FROM UnpivotedOT
)
-- 将行数据重新聚合为列，得到最终结果
SELECT 
    EmpID,
    Grouping,
    YearWeek,
    OT30,
    OT50,
    Remarks,
    ISNULL([OT30], 0) AS OT30_NT,
    ISNULL([OT50], 0) AS OT50_NT
FROM CalculatedOT
PIVOT (
    SUM(EffectiveOT)
    FOR Metric IN ([OT30], [OT50])
) AS PivotedResult
ORDER BY EmpID, Grouping, YearWeek;

关键逻辑说明 #

1. 拆分加班类型：用CROSS APPLY替代UNPIVOT，更灵活地保留原始列（如OT30、OT50、Remarks），同时给每个员工+薪资组+加班类型的记录按YearWeek排序生成行号。
2. 分段累计计算：
   • PreviousCumulative：用窗口函数计算到当前行的上一行累计时长，避免直接计算全量累计导致的负数问题。
   • EffectiveOT：分两种情况判断：
     • 若之前累计已达20小时，当前行有效时长为0；
     • 若加上当前时长会超过20，则取剩余额度（20-之前累计），否则取原时长。
3. 重新聚合列：通过PIVOT将拆分后的行数据转回列格式，得到最终的OT30_NT和OT50_NT列。

解决你之前的问题 #

你之前的CalcAttempt出现负数，是因为直接用20 - 累计总和，当累计总和超过20时就会得到负值。本方案通过先判断上一行的累计是否已达上限，再计算当前行的有效时长，彻底避免了负数，同时严格控制累计不超过20小时。

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