Join Query Issue 1

[igrummett]

I have 2 tables in my database.

Table A
ProjectWeek Employee AllocationHours AllocationCost
1 Ian 2 222
1 Bob 3 333
1 Steve 1 111
2 Ian 9 999

Table B
ProjectWeek Employee TimesheetHours TimesheetCost
1 Ian 63 6999.93
1 Steve 4 40
3 Derek 8 85

I have tried many ways to join these tables to get the following result: -

ProjectWeek Employee AllocationHours AllocationCost TimesheetHours TimesheetCost
1 Ian 2 222 63 6999.93
1 Bob 3 333 0 0
1 Steve 1 111 4 40
2 Ian 9 999 0 0
3 Derek 0 0 8 85

Basically I want a query that groups my combined results by Project Week and Employee.

Any help much appreciated

 

[r937]

SELECT a.ProjectWeek
     , a.Employee
     , a.AllocationHours
     , a.AllocationCost
     , COALESCE(b.TimesheetHours,0) AS TimesheetHours
     , COALESCE(b.TimesheetCost,0)  AS TimesheetCost
  FROM TableA AS a
LEFT OUTER
  JOIN TableB AS b
    ON b.ProjectWeek = a.ProjectWeek
   AND b.Employee = a.Employee

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[igrummett]

Hi,

Thanks for the feedback. I have tried this SQL and it almost works. The only problem is that it does not bring back and results for Derek in week 3 who only has an entry on Table B.

Any further ideas would be great.

 

[r937]

SELECT ProjectWeek
     , Employee
     , SUM(ah) AS AllocationHours
     , SUM(ac) AS AllocationCost
     , SUM(th) AS TimesheetHours
     , SUM(tc) AS TimesheetCost
  FROM ( SELECT ProjectWeek
              , Employee
              , AllocationHours AS ah
              , AllocationCost  AS ac
              , 0 AS th
              , 0 AS tc
           FROM TableA
         UNION ALL
         SELECT ProjectWeek
              , Employee
              , 0 AS ah
              , 0 AS ac
              , TimesheetHours AS th
              , TimesheetCost  AS tc
           FROM TableB
       ) AS u
GROUP
    BY ProjectWeek
     , Employee

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon