SQL Server Query that Returns Summarized Data

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)
from labor
left outer join timesheet on people.personid = timesheet.personid
group by people.personid, people.shiftid
SQL-Result-Example.docx
cbridgmanAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
Don't have any sample data to test with, but something like this should work, or at least be very close:

DECLARE @StartDay date
DECLARE @EndDay date

SET @StartDay = '20170701'
SET @EndDay = '20170715'

SELECT TS.PERSONID, P.SHIFTID, TS.HOURS_WORKED, SS.HOURS_AVAILABLE
FROM (
    SELECT PERSONID, SUM(HOURS_WORKED) AS HOURS_WORKED
    FROM dbo.TIMESHEET
    WHERE DATE >= @StartDay AND DATE < DATEADD(DAY, 1, @EndDay)
    GROUP BY PERSONID
) AS TS
INNER JOIN dbo.PEOPLE P ON P.PERSONID = TS.PERSONID
INNER JOIN (
    SELECT SHIFTID, SUM(HOURS_AVAILABLE) AS HOURS_AVAILABLE
    FROM dbo.SHIFTSCHEDULE
    WHERE DATE >= @StartDay AND DATE < DATEADD(DAY, 1, @EndDay)
    GROUP BY SHIFTID
) AS SS ON SS.SHIFTID = P.SHIFTID
ORDER BY PERSONID
cbridgmanAuthor Commented:
Thanks Scott, I will give this a shot and let you know
Scott PletcherSenior DBACommented:
Great.  If you want to list everyone, including those who did not work at all during that period, try this:

DECLARE @StartDay date
DECLARE @EndDay date

SET @StartDay = '20170701'
SET @EndDay = '20170715'

SELECT P.PERSONID, P.SHIFTID, ISNULL(TS.HOURS_WORKED, 0) AS HOURS_WORKED,
    SS.HOURS_AVAILABLE
FROM dbo.PEOPLE P
INNER JOIN (
    SELECT SHIFTID, SUM(HOURS_AVAILABLE) AS HOURS_AVAILABLE
    FROM dbo.SHIFTSCHEDULE
    WHERE DATE >= @StartDay AND DATE < DATEADD(DAY, 1, @EndDay)
    GROUP BY SHIFTID
) AS SS ON SS.SHIFTID = P.SHIFTID
LEFT OUTER JOIN (
    SELECT PERSONID, SUM(HOURS_WORKED) AS HOURS_WORKED
    FROM dbo.TIMESHEET
    WHERE DATE >= @StartDay AND DATE < DATEADD(DAY, 1, @EndDay)
    GROUP BY PERSONID
) AS TS ON TS.PERSONID = P.PERSONID
ORDER BY PERSONID

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

cbridgmanAuthor Commented:
Hi Scott,

It appears that the first script worked. I haven't tried the 2nd one yet. I will let you know.

thanks for your help thus far
cbridgmanAuthor Commented:
Hi Scott,

Your solutions worked perfectly. Thanks very much for your help.
cbridgmanAuthor Commented:
Thanks a lot. Never would have figured that out without your help
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.