如何在SQL中对同Company_Year_Week下的两类SUM小时数求和

根据你的需求，这里提供几种不同场景的实现方式：

1. 将同一用户同周的T/A状态合并为一行总和 #

如果需要把同一用户、同一周内的'T'和'A'状态工时合并成一行展示，同时保留其他状态的单独行，可以通过修改分组逻辑实现：

SELECT 
    R.ResourceID,
    R.EmailAddr,
    CAL.Company_Year_Week,
    CAL.Company_AP,
    -- 把T和A合并为统一标识，其他状态保留原代码
    CASE WHEN T.StatusCode IN ('T', 'A') THEN 'T+A' ELSE T.StatusCode END AS StatusCode,
    SUM(T.StandardHours) AS "Total Hours"
FROM PMGB.PM.CompanyAPCalendar AS CAL
JOIN E4GB.dbo.Time AS T ON CAST(CAL.full_date AS DATE) = CAST(T.TimeEntryDate AS DATE)
JOIN E4GB.dbo.Resource AS R ON T.ResourceID = R.ResourceID
JOIN E4GB.dbo.ResourceWorkHourGroup AS WHG ON WHG.ResourceID = R.ResourceID
WHERE T.StatusCode != 'V'
  AND R.EmailAddr IS NOT NULL
GROUP BY 
    R.ResourceID,
    R.EmailAddr,
    CAL.Company_Year_Week,
    CAL.Company_AP,
    CASE WHEN T.StatusCode IN ('T', 'A') THEN 'T+A' ELSE T.StatusCode END;

核心逻辑是用CASE语句将'T'和'A'映射为同一个分组键，这样GROUP BY时会自动累加这两类状态的工时。

2. 保留原状态行，同时显示同周T/A总工时 #

如果需要保留每个状态的单独行，但额外显示该用户该周内T/A状态的总工时，可以使用窗口函数：

SELECT 
    R.ResourceID,
    R.EmailAddr,
    CAL.Company_Year_Week,
    CAL.Company_AP,
    T.StatusCode,
    SUM(T.StandardHours) AS "Total Hours",
    -- 计算当前用户当前周内T/A状态的总工时
    SUM(CASE WHEN T.StatusCode IN ('T', 'A') THEN T.StandardHours ELSE 0 END) 
        OVER(PARTITION BY R.ResourceID, CAL.Company_Year_Week) AS "T+A Total Hours"
FROM PMGB.PM.CompanyAPCalendar AS CAL
JOIN E4GB.dbo.Time AS T ON CAST(CAL.full_date AS DATE) = CAST(T.TimeEntryDate AS DATE)
JOIN E4GB.dbo.Resource AS R ON T.ResourceID = R.ResourceID
JOIN E4GB.dbo.ResourceWorkHourGroup AS WHG ON WHG.ResourceID = R.ResourceID
WHERE T.StatusCode != 'V'
  AND R.EmailAddr IS NOT NULL
GROUP BY 
    R.ResourceID,
    R.EmailAddr,
    CAL.Company_Year_Week,
    CAL.Company_AP,
    T.StatusCode;

窗口函数SUM() OVER(PARTITION BY ...)会在指定的分组（用户+周）内，累加符合条件的工时，作为额外列返回。

3. 仅获取T/A状态的周汇总行 #

如果只需要同一用户同周的T/A总工时，不需要其他状态的数据，可以直接过滤状态后分组：

SELECT 
    R.ResourceID,
    R.EmailAddr,
    CAL.Company_Year_Week,
    CAL.Company_AP,
    'T+A' AS StatusCode,
    SUM(T.StandardHours) AS "Total Hours"
FROM PMGB.PM.CompanyAPCalendar AS CAL
JOIN E4GB.dbo.Time AS T ON CAST(CAL.full_date AS DATE) = CAST(T.TimeEntryDate AS DATE)
JOIN E4GB.dbo.Resource AS R ON T.ResourceID = R.ResourceID
JOIN E4GB.dbo.ResourceWorkHourGroup AS WHG ON WHG.ResourceID = R.ResourceID
WHERE T.StatusCode IN ('T', 'A')
  AND R.EmailAddr IS NOT NULL
GROUP BY 
    R.ResourceID,
    R.EmailAddr,
    CAL.Company_Year_Week,
    CAL.Company_AP;

这个方案直接筛选出'T'和'A'状态的数据，然后按用户和周分组求和。

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