CTE按不同日期周期聚合问题：计划工时计算错误求助

现有三张数据表EarnedQ、PlannedQ、PeriodQ，尝试用CTE按指定日期周期聚合工时，但SQL返回的计划工时数值过高。PlannedQ为周度数据，需基于PeriodQ实现动态周期（如双周、月度）聚合，调整分组和聚合方式后仍未解决，请求修正。

样本数据 #

EarnedQ #

PlannedQ #

PeriodQ #

现有SQL #

WITH AggregatedEarnedHours AS (
    SELECT
        WBS,
        ActivityID,
        Period_WE,
        SUM(EarnedMH_I) AS EarnedHours
    FROM
        EarnedQ
    GROUP BY
        WBS,
        ActivityID,
        Period_WE
),
AggregatedPlannedHours AS (
    SELECT
        AID,
        EOW2 AS Period_WE,
        SUM(Units) AS PlannedHours
    FROM 
        PlannedQ T
        JOIN PeriodQ P ON T.Weekending = P.Period_WE
    GROUP BY
        AID,
        EOW2
)
SELECT
    E.WBS,
    E.ActivityID,
    E.Period_WE,
    SUM(E.EarnedHours) AS EarnedHours,
    ISNULL(P.PlannedHours, 0) AS PlannedHours
FROM 
    AggregatedEarnedHours E
LEFT JOIN AggregatedPlannedHours P ON E.ActivityID = P.AID AND E.Period_WE = P.Period_WE
GROUP BY 
    E.WBS,
    E.ActivityID,
    E.Period_WE,
    P.PlannedHours
HAVING (SUM(E.EarnedHours) != 0 OR SUM(P.PlannedHours) !=0) AND 
    SUM(E.EarnedHours) > 0

当前输出 #

期望输出 #

修正方案及代码 #

问题分析 #

1. 关联条件错误：AggregatedPlannedHours中PlannedQ与PeriodQ的关联逻辑颠倒，应该用PlannedQ.Period_WE（原始周结束日）关联PeriodQ.Weekending，再按PeriodQ.Period_WE（目标聚合周期）分组。
2. 字段名不匹配：现有SQL中使用了AID、EOW2、Units等不存在的字段，需对应到实际表字段ActivityID、PeriodQ.Period_WE、PlannedQ.EarnedMH_I。
3. 冗余聚合：AggregatedEarnedHours已经按周期完成聚合，外层查询无需再次SUM(E.EarnedHours)。

修正后的SQL #

WITH AggregatedEarnedHours AS (
    SELECT
        WBS,
        ActivityID,
        Period_WE,
        SUM(EarnedMH_I) AS EarnedHours
    FROM
        EarnedQ
    GROUP BY
        WBS,
        ActivityID,
        Period_WE
),
AggregatedPlannedHours AS (
    SELECT
        T.ActivityID,
        P.Period_WE,
        SUM(T.EarnedMH_I) AS PlannedHours
    FROM 
        PlannedQ T
        JOIN PeriodQ P ON T.Period_WE = P.Weekending
    GROUP BY
        T.ActivityID,
        P.Period_WE
)
SELECT
    E.WBS,
    E.ActivityID,
    E.Period_WE AS WeekEnding,
    E.EarnedHours AS EarnedMH_I,
    ISNULL(P.PlannedHours, 0) AS Units
FROM 
    AggregatedEarnedHours E
LEFT JOIN AggregatedPlannedHours P 
    ON E.ActivityID = P.ActivityID 
    AND E.Period_WE = P.Period_WE
WHERE E.EarnedHours > 0

验证结果 #

修正后的SQL会输出与期望一致的结果：

• 5/27/2023周期的计划工时为41.36 + 41.36 = 82.72
• 6/10/2023周期的计划工时为33.09 + 41.36 = 74.45

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