I am attempting to write some SQL (SQL Server) that will return values from 3 related tables:
1. PEOPLE – this table contains one row for every person in an Organization and indicates which shift each person works on (e.g., 1st shift, 2nd shift, etc.).
Columns in this table are PERSONID, SHIFTID
2. TIMESHEET – this table stores daily time sheet information for the people in the organization. There is one row per person, per day that indicates, for any given day, how many hours a person worked.
Columns in this table are PERSONID, DATE, HOURS_WORKED.
3. SHIFTSCHEDULE – this table contains information that tells you on any given day, how many hours that people on a given shift have available to work. For example, one row in the table will tell you that anyone on the 1st shift is available for 7 hours on July 1, 2017. Another row will tell you that anyone on the 1st shift is available for 9 hours on July 2, 2017, and so on.
Columns in this table are SHIFTID, DATE, HOURS_AVAILABLE
What I am attempting to pull out of the database is a list of people together with (a) the total number of hours they have worked over a defined period (say July 1, 2017 through July 15, 2017) and (b) the total number of hours that they were available to work during that same time frame.
I've attached an example of what I am attempting to return. I'm able to return the data that I'm looking for when I run against two table (PEOPLE and TIMESHEET) but I don't know how to get correct results from the 3rd table (SHIFTSCHEDULE). The SQL that I've run successfully is shown below. Would someone be able to help me getting the 3rd table added to this?
select people.personid, people.shiftid, sum(timesheet.hours_worked
left outer join timesheet on people.personid = timesheet.personid
group by people.personid, people.shiftid