SQL Server CTE Aggregation by 2 Different Date Periods

5vf7fwbs 于 2023-08-02 发布在其他

关注(0)|答案(1)|浏览(83)

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.

sql-server

来源：https://stackoverflow.com/questions/76816084/cte-aggregation-by-2-different-date-periods

1条答案

按热度按时间

jaql4c8m1#

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

赞(0）回复(0）举报 2023-08-02

我来回答

SQL Server CTE Aggregation by 2 Different Date Periods

1条答案

相关问题

热门标签

最新问答