I am having a problem with a query that joins the following tables. I want
to create a query that would tell me how many Manhours have been alloted for
a particular
project (32) by category and how many hours (from the Tasks table) have
actually been used for this project and category.
The data tables look like this:
Manhours
mid | ProjectId |ChargeNum | Catid | Hours | EmployeeId
1 | 32 |111111 | 19 | 80 |200020
2 | 32 |111111 | 19 | 24
|200003
Tasks
TaskID |ChargeNum |Catid |EmployeeId |Hours
1 |111111 |19 |200020 |8
2 |111111 |19 |200020 |8
5 |111111 |19 |200003 |12
By looking at the Manhours table I can tell that for ProjectId 32 I have 104
(80+24) hours alloted for Catid 19
By looking at the Tasks table I can tell that 36 hours (8 + 8 + 8 + 12) have
been used on this project (same ChargeNum as Manhours table)
and Catid 19
I would like to creat a query that would give me the following results
Catid |allotedhours |usedHours
19 |104 |28
I have tried joining the tables in a variety of ways but I am still not
getting the correct results. I seem to have some problems with the hours
alloted calculating correctly.
thanks,
Luis
