SQL Query - Get Sums from Two Different Levels of Hierarchy

Question

A Project has multiple Tasks, which has multiple Assignments

Projects (1-n) -> Tasks (1-n) -> Assignments

A field on the Tasks table is Planned Hours.

A field on the Assignments table is Assigned Hours.

How do I get the Planned Hours and Assigned Hours for all Projects in a single query?

Answer 1

EDIT: This answer gives incorrect results but I will leave it up for historical purposes.

I think this will help you out. I can't figure out how to get rid of the Distinct though.

Select Distinct  
        Project.ProjectId,
        Sum(Task.PlannedHours) 
            Over (Partition By Project.ProjectId) As PlannedHours,
        Sum(Assignment.AssignedHours) 
            Over (Partition By Project.ProjectId) As AssignedHours
From Project 
    Inner Join Task On Project.ProjectId = Task.ProjectId   
    Inner Join Assignment On Task.TaskId= Assignment.AssignmentId

Here is my test script.

Declare @Project Table (ProjectId Int)
Declare @Task Table (TaskId Int, ProjectId Int, PlannedHours Int)
Declare @Assignment Table (AssignmentId Int, TaskId Int, AssignedHours Int)

Insert Into @Project (ProjectId) values (1)
Insert Into @Project (ProjectId) values (2)
Insert Into @Project (ProjectId) values (3)

Insert Into @Task (TaskId, ProjectId, PlannedHours) values (1, 1, 10)
Insert Into @Task (TaskId, ProjectId, PlannedHours) values (2, 1, 10)
Insert Into @Task (TaskId, ProjectId, PlannedHours) values (3, 2, 11)
Insert Into @Task (TaskId, ProjectId, PlannedHours) values (4, 3, 12)

Insert Into @Assignment (AssignmentId, TaskId, AssignedHours) values (1, 1, 2)
Insert Into @Assignment (AssignmentId, TaskId, AssignedHours) values (2, 1, 2)
Insert Into @Assignment (AssignmentId, TaskId, AssignedHours) values (3, 2, 2)
Insert Into @Assignment (AssignmentId, TaskId, AssignedHours) values (4, 3, 3)

Select Distinct 
        Project.ProjectId,
        Sum(Task.PlannedHours) 
            Over (Partition By Project.ProjectId) As PlannedHours,
        Sum(Assignment.AssignedHours) 
            Over (Partition By Project.ProjectId) As AssignedHours   
From @Project Project 
    Inner Join @Task Task On Project.ProjectId = Task.ProjectId   
    Inner Join @Assignment Assignment On Task.TaskId= Assignment.AssignmentId

Answer 2

select p.ProjectID, t.PlannedHoursSum, a.AssignedHoursSum
from Projects p
inner join (
    select ProjectID, sum(PlannedHours) as PlannedHoursSum
    from Tasks
    group by ProjectID
) t on p.ProjectID = t.ProjectID
inner join (
    select t.ProjectID, sum(AssignedHours) as AssignedHoursSum
    from Tasks t
    inner join Assignments a on t.TaskID = a.TaskID
    group by t.ProjectID
) a on p.ProjectID = a.ProjectID