Simplifying my question:
I have 2 tables. One table has workers' availability by date: there are columns for the date, the worker's name, and the number of hours they are available that date. On the other table, there are columns for the date, the worker's name, a project name, and the number of hours the worker has been allocated to that project on that date. In the first table, there is only one row per worker-date whereas in the second table there are multiple rows per worker-date because a worker may have hours allocated to more than one project on a given day.
How can I add up the number of hours a worker has allocated to projects in a given day (table 2) and subtract that from the number of hours of availability a worker has in the same date (table 1)?
I am trying to help my coworker who is a project manager create a dashboard that shows resource availability by project. The data comes from Project Online. Basically, she wants a dashboard that shows availability (resource capacity days minus assignment work days) and is filterable by date.
The ResourceTimePhasedDataSet contains these columns: ResourceID, Date, and Capacity (Hours). The AssignmentTimePhasedDataSet has ResourceID, Date, ProjectID, ProjectName, and AssignmentWorkDays.
The problem is that the ResourceTimePhasedDataSet has 1 row for each working day for each resource whereas the AssignmentTimePhasedDataSet has 1 row for each day that a project has been assigned to a specific resource. I need to find out how to add up all the assignment hours for a specific resource by date and then subtract that from the resource's capacity for that day.
