CTE Aggregation by 2 Different Date Periods

Question

I have the following sample data and desired output, the SQL query I have written doesn't quite yield the results I am looking for and I can't understand why. I have tried different groupings, aggregating or non-aggregating the Units column, nothing seems to work. Any help would be greatly appreciated.

Sample Data:

EarnedQ:

ActivityID	EarnedMH_I	Period_WE
CS0001	206.79	5/27/2023
CS0001	41.35	6/10/2023

PlannedQ:

ActivityID	EarnedMH_I	Period_WE
CS0001	41.36	5/20/2023
CS0001	41.36	5/27/2023
CS0001	33.09	6/03/2023
CS0001	41.36	6/10/2023

PeriodQ:

Weekending	Period_WE
5/20/2023	5/27/2023
5/27/2023	5/27/2023
6/03/2023	6/10/2023
6/10/2023	6/10/2023

SQL I have tried:

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

Current Output:

ActivityID	EarnedMH_I	Units	WeekEnding
CS0001	206.79	579.04	5/27/2023
CS0001	41.35	521.15	6/10/2023

Desired Output:

ActivityID	EarnedMH_I	Units	WeekEnding
CS0001	206.79	82.72	5/27/2023
CS0001	41.35	74.45	6/10/2023

As you can see it almost works, but the planned hours are too high. I have a feeling it has to do with doing the aggregation or grouping incorrectly. Also, this data is abbreviated for sake of posting, there are more rows in all the tables but you get the idea. Note that PlannedQ is weekly data whereas I am trying to group biweekly here. A manual grouping by biweekly will not fit my solution, as I need to group by monthly in other cases too and need something dynamic, hence the inclusion of the PeriodQ table.

Answer 1

Data

CREATE TABLE EarnedQ(
   ActivityID VARCHAR(30) NOT NULL 
  ,EarnedMH_I NUMERIC(7,2) NOT NULL
  ,Period_WE  DATE  NOT NULL
);
INSERT INTO EarnedQ
(ActivityID,EarnedMH_I,Period_WE) VALUES 
('CS0001',206.79,'5/27/2023'),
('CS0001',41.35,'6/10/2023');
---------------------------------------------
CREATE TABLE PlannedQ(
   ActivityID VARCHAR(30) NOT NULL 
  ,EarnedMH_I NUMERIC(6,2) NOT NULL
  ,Period_WE  DATE  NOT NULL
);
INSERT INTO PlannedQ
(ActivityID,EarnedMH_I,Period_WE) VALUES 
('CS0001',41.36,'5/20/2023'),
('CS0001',41.36,'5/27/2023'),
('CS0001',33.09,'6/03/2023'),
('CS0001',41.36,'6/10/2023');
------------------------
CREATE TABLE PeriodQ(
   Weekending DATE  NOT NULL
  ,Period_WE  DATE  NOT NULL
);
INSERT INTO PeriodQ
(Weekending,Period_WE) VALUES 
('5/20/2023','5/27/2023'),
('5/27/2023','5/27/2023'),
('6/03/2023','6/10/2023'),
('6/10/2023','6/10/2023');

use Subquery to get your desired result

SELECT A.ActivityID,EarnedMH_I,Units,Weekending
FROM 
(select E.ActivityID,E.EarnedMH_I ,PE.Weekending
from EarnedQ E
JOIN PeriodQ PE
ON E.Period_WE=PE.Weekending 
AND E.Period_WE BETWEEN PE.Weekending AND  PE.Period_WE 
  ) A
JOIN 
(
select P.ActivityID,SUM(P.EarnedMH_I) Units,PE1.Period_WE
from PlannedQ P
JOIN PeriodQ PE1
ON P.Period_WE=PE1.Weekending AND 
P.Period_WE BETWEEN PE1.Weekending AND  PE1.Period_WE
GROUP BY P.ActivityID,PE1.Period_WE
) B
ON A.ActivityID=B.ActivityID AND Period_WE=Weekending

dbfiddle