1) Project: master data of all the below lying information
2) Project - Version: one to many relationship
3) Version - Availability: one to many relationship, for each version one or more resource would work and would have
different available percentage. Some may available for 100%, some may available for 50%, etc.
4) Version - Time sheet: one to many relationship, each resource will log their time spent on each project - version
Assume, we need to calculate actual availability of the hours per resource based on their availability % in Version - Availability data. For example if a resource available 100% we would be saying 5 days a week would sum into 40 hours (5 x 8 = 40).
Now, I want a report to show result by
Project - Version - Resource - Availability - Time logged - Time not entered
Project - from Project table
Version - from Project - Version table
Resource - from Version - Availability table
Availability - Calculated value from Version - Availability table
Time logged - from Version - Timesheet table
Time not entered - difference from Availability - Time logged
The resources may or may not exist in Version - Availability table. Similarly the resource may or may not enter their timesheet entry.
If the resource exist in the Version - Availability table, then we need the calculated Availability value (example 5 x 8 = 40) otherwise 8 hours
If the resource entered timesheet then we need the Time logged hours
We are able to get the report if all the resources exist in the Version - Availability table and entered their timesheet. But, we are unable to match the above two condition when the user is not exist in Version - Availability table but entered timesheet.
Please help me how to map - join the tables and the report
