asked on

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

Scott Pletcher

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

cbridgman

ASKER

Thanks Scott, I will give this a shot and let you know

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

cbridgman

ASKER

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