Help With MS SQL Query - Employee Adherence

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
dkilbyAsked:
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.

pcelbaCommented:
Where is the attached file?
dkilbyAuthor Commented:
I have attached again, hopefully comes through

EmployeeAdherence.xlsx
David Johnson, CD, MVPOwnerCommented:
we also need details on the two tables, Scheduled & Time Taken (Actual)
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

dkilbyAuthor 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 EngineerCommented:
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

dkilbyAuthor 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.
pcelbaCommented:
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

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
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.