I have a few questions on how to handle a few employee timekeeping queries in SQL 2012..
We are fed the data from our Point Of Sale Software Provider, so we cannot change the format of the data.
Work days are based on DateOfBusiness which are from 5:00 am until 4:59am the next calendar day, so it crosses midnight.
Employees must clock out for the 30 minute required break, so there are 2 or more records for one day for these employees. Not sure why, but several employees have more than 3 records in one day (EmployeeShiftNumber).
I need to report list of initial InTime and Final OutTime and the number of minutes worked. I have will have to compare these against the table which holds the employee schedules.
EmpID Date InTime OutTime Minutes
26047 11/27/2017 11:30 am 7:31 pm 448
526044 11/26/2017 7:30 pm 1:54 am 504
526044 11/27/2017 7:30 pm 1:47 am 507
529582 11/27/2017 12:02 pm 8:15 pm 461
530029 11/27/2017 4:45 pm 10:42 pm 357
Here is what the data table looks like:
select fkemployeenumber , DateOfBusiness, EmployeeshiftNumber, fkjobcodeid, InHour, InMinute, OutHour, OutMinute
from dpvhstshift
DateOfBusiness fkemployeenumber EmployeeshiftNumber fkjobcodeid InHour InMinute OutHour OutMinute
2017-11-27 00:00:00.000 26047 0 1 11 30 14 35
2017-11-27 00:00:00.000 26047 1 1 15 8 19 31
2017-11-25 00:00:00.000 526044 0 2 17 0 1 46
2017-11-26 00:00:00.000 526044 0 2 17 30 1 54
2017-11-27 00:00:00.000 526044 0 2 17 20 1 47
2017-11-25 00:00:00.000 529582 0 1 12 0 19 59
2017-11-27 00:00:00.000 529582 0 1 12 2 20 0
2017-11-27 00:00:00.000 529582 1 1 20 2 20 15
2017-11-27 00:00:00.000 530029 0 1 16 45 22 42