如何在Pivot查询中添加计算列并统计员工工时超时情况？

优化后查询代码 #

SELECT 
    p.EmployeeId,
    p.CheckDate,
    p.[Regular],
    p.[Overtime],
    -- 计算当期总实际工时（处理空值避免计算错误）
    ISNULL(p.[Regular], 0) + ISNULL(p.[Overtime], 0) AS TotalActualHours,
    -- 当期允许总工时
    a.Hours AS AllowableTotalHours,
    -- 工时差值：实际总工时 - 允许工时（正数为超时）
    (ISNULL(p.[Regular], 0) + ISNULL(p.[Overtime], 0)) - a.Hours AS HoursDifference,
    -- 统计该员工在指定时间范围内的超时总次数
    (SELECT COUNT(*) 
     FROM (
         SELECT 
             ISNULL(SUM(ae.CurHoursAmt), 0) AS CycleTotalHours
         FROM ARC_EARNINGS ae
         JOIN VAL_PAYCALENDAR vc ON vc.RowId = ae.PayCalendarId
         JOIN allowable2 a2 ON a2.Checkdate2 = vc.CheckDate
         WHERE ae.EmployeeId = @Employee
           AND vc.CheckDate BETWEEN '1/5/22' AND '1/5/23'
         GROUP BY vc.CheckDate
     ) cycleData
     WHERE cycleData.CycleTotalHours > a2.Hours) AS OvertimeCount
FROM
(
    SELECT 
        pm.EmployeeId,
        vc.CheckDate,
        ae.EarningsCd,
        ae.CurHoursAmt
    FROM PER_MASTER pm
    JOIN ARC_EARNINGS ae ON ae.EmployeeId = pm.EmployeeId
    JOIN VAL_PAYCALENDAR vc ON vc.RowId = ae.PayCalendarId
    WHERE vc.CheckDate BETWEEN '1/5/22' AND '1/5/23'
      AND pm.EmployeeID = @Employee
) t
PIVOT(
    SUM(t.CurHoursAmt)
    FOR t.EarningsCd IN ([Regular],[Overtime])
)p
JOIN allowable2 a ON a.Checkdate2 = p.CheckDate
ORDER BY p.CheckDate

关键优化点说明 #

• 核心指标计算：直接在SQL内完成总实际工时、工时差值、超时次数的计算，完全适配SSRS环境，无需依赖Excel
• 空值处理：用ISNULL处理无对应工时类型的空值，避免总工时计算出现错误
• 查询效率优化：将allowable2的关联逻辑移至PIVOT之后，减少子查询内的重复关联操作
• 超时次数统计：通过嵌套子查询按发薪周期聚合总工时，再统计总工时超过允许值的周期数量，一次性得到员工超时总次数

可选扩展 #

若需按年度区分允许工时的变化，可在查询中加入YEAR(p.CheckDate)字段用于分组展示，或在allowable2的关联条件中补充年度匹配逻辑

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