Home Schedule Adherence for Work force management
Reply: 0

Schedule Adherence for Work force management

user8793
1#
user8793 Published in September 19, 2018, 11:26 am

I am working on generating a Workforce management schedule adherence report using SQL.

I have 2 SQL tables: dbo.Scheduled_Shifts, and dbo.Actual_Work

Here is the sample data from both tables.

dbo.Scheduled_Shifts

EmpID     ScheduleType           StartTime                    EndTime
  1           Work         2018-03-01 09:00:00.000     2018-03-01 11:00:00.000
  1           Break        2018-03-01 11:00:00.000     2018-03-01 11:15:00.000
  1           Work         2018-03-01 11:15:00.000     2018-03-01 13:00:00.000
  1           Lunch        2018-03-01 13:00:00.000     2018-03-01 14:00:00.000
  1           Work         2018-03-01 14:00:00.000     2018-03-01 16:00:00.000

dbo.Actual_Work

EmpID     ActualWorkType           StartTime                    EndTime
  1           Work         2018-03-01 09:10:00.000     2018-03-01 10:00:00.000
  1           Fax          2018-03-01 10:00:00.000     2018-03-01 10:02:00.000
  1           Work         2018-03-01 10:02:00.000     2018-03-01 11:10:00.000
  1           Break        2018-03-01 11:10:00.000     2018-03-01 11:20:00.000
  1           Work         2018-03-01 11:20:00.000     2018-03-01 13:00:00.000
  1           Lunch        2018-03-01 13:00:00.000     2018-03-01 13:55:00.000
  1           Work         2018-03-01 13:55:00.000     2018-03-01 16:05:00.000

From these tables, I want to achieve if the employee followed (or stick) to the schedule.

Here is the dataset I am expecting to acheive:

EmpId    ActualWorkType       AW_StartTime                AW_EndTime            ScheduleType             Schd_StartTime             Schd_EndTime
  1          Work         2018-03-01 09:10:00.000    2018-03-01 10:00:00.000         Work           2018-03-01 09:00:00.000     2018-03-01 10:00:00.000
  1          Fax          2018-03-01 10:00:00.000    2018-03-01 10:02:00.000         Work           2018-03-01 10:00:00.000     2018-03-01 10:02:00.000
  1          Work         2018-03-01 10:02:00.000    2018-03-01 11:10:00.000         Work           2018-03-01 10:02:00.000     2018-03-01 11:00:00.000
  1          Break        2018-03-01 11:10:00.000    2018-03-01 11:20:00.000         Break          2018-03-01 11:00:00.000     2018-03-01 11:15:00.000
  1          Work         2018-03-01 11:20:00.000    2018-03-01 13:00:00.000         Work           2018-03-01 11:15:00.000     2018-03-01 13:00:00.000
  1          Lunch        2018-03-01 13:00:00.000    2018-03-01 13:55:00.000         Lunch          2018-03-01 13:00:00.000     2018-03-01 14:00:00.000
  1          Work         2018-03-01 13:55:00.000    2018-03-01 16:05:00.000         Work           2018-03-01 14:00:00.000     2018-03-01 16:00:00.000

I would appreciate algorithm, or psuedo-code or SQL query to solve it.

EDIT: Adding query to generate table and sample data

create table #Scheduled_Shifts
(
    EmpId int,
    ScheduleType varchar(50),
    StartTime datetime,
    EndTime datetime
)

insert into #Scheduled_Shifts values
(1, 'Work', '2018-03-01 09:00:00.000','2018-03-01 11:00:00.000'),
(1, 'Break', '2018-03-01 11:00:00.000', '2018-03-01 11:15:00.000'),
(1, 'Work', '2018-03-01 11:15:00.000', '2018-03-01 13:00:00.000'),
(1, 'Lunch', '2018-03-01 13:00:00.000', '2018-03-01 14:00:00.000'),
(1, 'Work', '2018-03-01 14:00:00.000', '2018-03-01 16:00:00.000')


create table #Actual_Work
(
    EmpId int,
    ActualWorkType varchar(50),
    StartTime datetime,
    EndTime datetime
)

insert into #Actual_Work values
 (1,'Work','2018-03-01 09:10:00.000','2018-03-01 10:00:00.000'),
 (1,'Fax', '2018-03-01 10:00:00.000','2018-03-01 10:02:00.000'),
 (1,'Work','2018-03-01 10:02:00.000','2018-03-01 11:10:00.000'),
 (1,'Break','2018-03-01 11:10:00.000','2018-03-01 11:20:00.000'),
 (1,'Work','2018-03-01 11:20:00.000','2018-03-01 13:00:00.000'),
 (1,'Lunch','2018-03-01 13:00:00.000','2018-03-01 13:55:00.000'),
 (1,'Work','2018-03-01 13:55:00.000','2018-03-01 16:05:00.000')
share|improve this question
  • Please read this for some tips on improving your question. It suggests things like showing the results you seek, showing the work you've done, ... . – HABO Apr 14 at 2:07

1 Answer 1

active oldest votes
up vote 0 down vote accepted
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.361059 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO