How do I combine an Aggregate query and 2 Joins? 1

[rvancleef]

I need to get an output that combines values from 2 tables based on StaffID and ProjectID. From the Timesheet table I need the total number of hours a staff spent on a project. From the Assignment table I need to show the estimated hours per staff. From the Staff table, I need to show the staff name.

While I can individually get the right results from the following 2 queries, I do not know how to combine them into 1 SQL Statement that joins all 3 tables while displaying the appropriate Aggregate value from TimeSht.


Declare @ProjID int
Select @ProjID=2003003

SELECT ITstaff.Staff_ID,ITstaff.FName,ITstaff.Lname,AS tHours
FROM ITstaff
LEFT JOIN ITstaff ON TimeSht.StaffID=ITstaff.Staff_ID
WHERE TimeSht.ProjID=@ProjID

SELECT Sum(TimeSht.NumberOfHours)
FROM TimeSht
WHERE TimeSht.ProjID=@ProjID
GROUP BY TimeSht.StaffID

Any Ideas?

 

[swampBoogie]

Declare @ProjID int
Select @ProjID=2003003

SELECT ITstaff.Staff_ID,ITstaff.FName,ITstaff.Lname,
(SELECT Sum(TimeSht.NumberOfHours)
FROM TimeSht
WHERE TimeSht.ProjID = @ProjID
  and staffid = ITstaff.staffid
 )AS tHours   ,
assignment.estimate
FROM ITstaff 
LEFT JOIN assignment ON assignment.StaffID=ITstaff.Staff_ID  
WHERE TimeSht.ProjID=@ProjID

Your first query looks a bit strange so I guessed somewhat with regard to the assignment table you mentioned.

 

[rvancleef]

I am getting an error:
Server: Msg 107, Level 16, State 3, Line 4
The column prefix 'TimeSht' does not match with a table name or alias name used in the query.

I tweaked the code with the precise table/field names:
Declare @ProjID int
Select @ProjID=2003003

SELECT ITstaff.Staff_ID,ITstaff.FName,ITstaff.Lname,
(SELECT Sum(TimeSht.NumberOfHours)
FROM TimeSht
WHERE TimeSht.ProjID = @ProjID
and TimeSht.StaffID = ITstaff.Staff_ID
)AS tHours ,
Assignment.EstHours
FROM ITstaff
LEFT JOIN assignment ON assignment.StaffID=ITstaff.Staff_ID
WHERE TimeSht.ProjID=@ProjID

 

[swampBoogie]

I changed the the last line using assignment instead.

SELECT ITstaff.Staff_ID,ITstaff.FName,ITstaff.Lname,
(SELECT Sum(TimeSht.NumberOfHours)
FROM TimeSht
WHERE TimeSht.ProjID = @ProjID
  and TimeSht.StaffID = ITstaff.Staff_ID
 )AS tHours ,
Assignment.EstHours
FROM ITstaff
LEFT JOIN assignment ON assignment.StaffID=ITstaff.Staff_ID  
WHERE Assignment.ProjID=@ProjID

 

[rvancleef]

Works PERFECTLY!!!!

Thanks!!!!!