Help With MS SQL Query - Employee Adherence

dkilby
dkilby used Ask the Experts™
on
I am trying to calculate employee adherence and have two tables in SQL, 1 table is for the employees daily schedule, the other is the times they actually take their scheduled items.

Scheduled Table contains the following fields

Employee Name
Scheduled Task
Starttime
EndTime

Times Taken table contains the following fields

Employee Name
Task
StartTime
EndTime

I want to try and pull data so it shows like the attached file, is there any way of doing this?

also in the query calculate the difference in minutes between the scheduled start time and taken start time and scheduled end time and take end time.  If there isnt an task scheduled then it would be the time between the taken end time and taken start time.

Thanks for the help
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Where is the attached file?

Author

Commented:
I have attached again, hopefully comes through

EmployeeAdherence.xlsx
Top Expert 2016

Commented:
we also need details on the two tables, Scheduled & Time Taken (Actual)
Ensure you’re charging the right price for your IT

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

Author

Commented:
Scheduled Table contains the following fields

Employee Name - varchar(1500
Scheduled Task - varchar(50)
Starttime - datetime
EndTime - datetime

Times Taken table contains the following fields

Employee Name - varchar(150)
Task - varchar(50)
StartTime - datetime
EndTime - datetime

Example of data in spreadsheet, hope that helps, if not please let me know.
SharathData Engineer

Commented:
try this.
SELECT t1.EmployeeName AS AgentName
       ,t1.ScheduledTask AS Status
       ,t1.Starttime AS ScheduledStartTime 
       ,t1.Endtime AS ScheduledEndTime
       ,t2.StartTime AS TakenStartTime
       ,t2.EndTime AS TakenEndTime
       ,convert(varchar, dateadd(ms, datediff(second, t1.StartTime, t2.StartTime) * 1000, 0), 114) AS StartTimeDiff
       ,convert(varchar, dateadd(ms, datediff(second, t1.EndTime, t2.EndTime) * 1000, 0), 114) AS EndTimeDiff
  FROM Table1 t1
  JOIN Table2 t2 
    ON t1.EmployeeName = t2.EmployeeName

Open in new window

Author

Commented:
This does not match up the dates in table 2 - time task was actually done, plus i need entries from both to show even if the matching entry is not in the other table.
First of all your data model is not perfect but it still allows some data manipulations...

Following code expects to define daily schedule for each Employee, each Scheduled Task must have unique name and also Employees must have unique name.  Taken Time can contain any number of "unknown tasks". Also each task should have date defined which is omitted in the below code sample. To calculate some time differences should then be easy.

You may play with this code in SSMS:
CREATE TABLE #Scheduled  (EmployeeName varchar(150), ScheduledTask varchar(50), StartTime datetime, EndTime datetime)
CREATE TABLE #TimesTaken (EmployeeName varchar(150), Task varchar(50), StartTime datetime, EndTime datetime)

INSERT INTO #Scheduled VALUES 
  ('Employee 1', 'Break 1', '07:45:00', '08:00:00'), 
  ('Employee 1', 'Project Time 1', '09:00:00', '10:00:00'), 
  ('Employee 1', 'Meal', '10:00:00', '10:30:00'), 
  ('Employee 1', 'Project Time 2', '11:00:00', '12:00:00'), 
  ('Employee 1', 'Break 2', '12:15:00', '12:30:00'), 
  ('Employee 2', 'Break 1', '15:45:00', '16:00:00')

INSERT INTO #TimesTaken VALUES 
  ('Employee 1', 'Break 1', '08:02:24', '08:16:49'), 
  ('Employee 1', 'Meal', '09:56:50', '10:27:45'), 
  ('Employee 1', 'Personal', '11:17:30', '11:20:20'), 
  ('Employee 1', 'Meeting', '11:26:08', '11:55:30'), 
  ('Employee 1', 'Break 2', '12:39:05', '12:47:37'), 
  ('Employee 1', 'Personal', '12:32:21', '12:34:29'), 
  ('Employee 2', 'Break 1', '08:02:24', '08:16:49'), 
  ('Employee 2', 'Meal', '09:56:50', '10:27:45'), 
  ('Employee 2', 'Personal', '11:17:30', '11:20:20'), 
  ('Employee 2', 'Meeting', '11:26:08', '11:55:30') 

SELECT ISNULL(t1.EmployeeName, t2.EmployeeName) [Agent Name], ISNULL(t1.ScheduledTask, t2.Task) [Status], 
       t1.StartTime [Scheduled StartTime], t1.EndTime [Scheduled EndTime], 
       t2.StartTime [Taken StartTime], t2.EndTime [Taken EndTime], ISNULL(t1.StartTime, t2.StartTime) forSort
  FROM #Scheduled t1
  FULL JOIN #TimesTaken t2 ON t1.ScheduledTask = t2.Task AND t1.EmployeeName = t2.EmployeeName AND CAST(t1.StartTime AS date) = CAST(t2.StartTime AS date)
ORDER BY [Agent Name], forSort

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial