Calculate and display time keeping when hours and minutes are in different columns

Transact-SQL
1

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

2

If you are wondering why you don't have any responses, try posting consumable data in the form of create table statement with insert statements of sample data, what you have tried so far, any errors you get, and desired result given the sample data you provide.

3

Here is the samples.

CREATE TABLE #Shift(
FKEmployeeNumber int,
DateOfBusiness datetime,
FKStoreId int,
EmployeeShiftNumber int,
FKJobCodeId int,
InHour int,
InMinute int,
OutHour int,
OutMinute int)

insert into #Shift ( FKEmployeeNumber, DateOfBusiness, FKStoreId, EmployeeShiftNumber, FKJobCodeId, InHour, InMinute,OutHour,OutMinute)
values

(26047, '11/27/2017', 3013,0, 1, 11, 30, 14, 35),
(26047, '11/27/2017', 3013, 1, 1, 15, 8, 19, 31),
(526044, '11/25/2017', 3013, 0, 2, 17, 0, 1, 46),
(526044, '11/26/2017', 3013, 0, 2, 17, 30, 1, 54),
(526044, '11/27/2017', 3013, 0, 2, 17, 20, 1, 47),
(529582, '11/25/2017', 3013, 0, 1, 12, 0, 19, 59),
(529582, '11/27/2017', 3013, 0, 1, 12, 2, 20, 0),
(529582, '11/27/2017', 3013, 1, 1, 20, 2, 20, 15),
(530029, '11/27/2017', 3013, 0, 1, 16, 45, 22, 42)

Select sh.FKEmployeeNumber, sh.DateOfBusiness, sh.FKStoreId, sh.EmployeeShiftNumber, sh.FKJobCodeId,
ActualStart=CONVERt(smalldatetime, replace(str(sh.inhour,2,0),' ','0')+':'+ replace(str(sh.inminute,2,0),' ','0')),
ActualEnd=CONVERT(smalldatetime, replace(str(sh.outhour,2,0),' ','0')+':'+ replace(str(sh.outminute,2,0),' ','0')),

CASE WHEN DATEDIFF(mi,convert(datetime, CONVERT( TIME, replace(str(sh.inhour,2,0),' ','0')+':'+ replace(str(sh.inminute,2,0),' ','0'))),convert(datetime, CONVERT( TIME, replace(str(sh.outhour,2,0),' ','0')+':'+ replace(str(sh.outminute,2,0),' ','0')))) < 0
THEN DATEDIFF(mi,convert(datetime, CONVERT( TIME, replace(str(sh.inhour,2,0),' ','0')+':'+ replace(str(sh.inminute,2,0),' ','0'))),DATEADD(day, 1,convert(datetime, CONVERT( TIME, replace(str(sh.outhour,2,0),' ','0')+':'+ replace(str(sh.outminute,2,0),' ','0')))))
ELSE DATEDIFF(mi,convert(datetime, CONVERT( TIME, replace(str(sh.inhour,2,0),' ','0')+':'+ replace(str(sh.inminute,2,0),' ','0'))),convert(datetime, CONVERT( TIME, replace(str(sh.outhour,2,0),' ','0')+':'+ replace(str(sh.outminute,2,0),' ','0'))))
END as ActMinutes
from #shift sh

I am trying to get the times to be shown in AM/PM format, and combine when an employee has 2 shifts in a day to show the initial clockin and the final clock out as shown in the initial post.

4

I think I've got the code to meet the basic requirements below. But I'm still confused. One, to me, the sample data doesn't match the output. Two, still not sure specifically what output you want. Finally:
Do you need to split the time to different WorkDays if the InTime is before 5 and/or the OutTime is after 5? None of the sample records have this issue, so maybe it will never come up. Would a start time of 1AM and and end time of 11AM need to be counted in two separate Work Dates?

SELECT FKEmployeeNumber AS EmpID, DateOfBusiness AS Date, 
    RIGHT(CONVERT(varchar(30), DATEADD(MINUTE, InTime_Mins, 0), 100), 7) AS InTime,
    RIGHT(CONVERT(varchar(30), DATEADD(MINUTE, OutTime_Mins, 0), 100), 7) AS OutTime,
    CONVERT(varchar(5), DATEADD(MINUTE, MinsWorked, 0), 8) AS TimeWorked
FROM (
    select sh.FKEmployeeNumber, sh.DateOfBusiness, 
        MIN(sh.InHour*60+InMinute) AS InTime_Mins,
        MAX(sh.OutHour*60+OutMinute) AS OutTime_Mins,
        SUM(((sh.outhour+case when sh.OutHour < sh.InHour then 24 else 0 end)*60 + sh.outminute) -
             (sh.inhour*60 + sh.inminute)) AS MinsWorked
    from #shift sh
    group by sh.FKEmployeeNumber, sh.DateOfBusiness
) AS derived
order by FKEmployeeNumber, DateOfBusiness
1 Like
5

Scott,
Thank you for the help. This is just a small piece of a much larger query. I was struggling with getting the one row per employee when they would clock out for a lunch break or some other split shift. I will be adding a number of other joins and conditions on the query.

Answers to your questions:

  1. I have added a new set of data below. There seems to be an issue displaying the final out time when the shift crosses midnight. employee #23761 should show 2:39 am as the out time, your script returns 9:30 PM

Blockquoteinsert into #Shift ( FKEmployeeNumber, DateOfBusiness, FKStoreId, EmployeeShiftNumber, FKJobCodeId, InHour, InMinute,OutHour,OutMinute)
values
(23761, '11/30/2017', 3013, 0, 1, 17, 39, 21, 30),
(23761, '11/30/2017', 3013, 1, 1, 21, 30, 2, 39),
(23770, '11/30/2017', 3013, 0, 200, 7, 19, 16, 25),
(23938, '11/30/2017', 3013, 0, 1, 16, 4, 1, 26),
(24006, '11/30/2017', 3013, 0, 1, 7, 30, 18, 36),
(24006, '11/30/2017', 3013, 1, 1, 18, 36, 18, 40),
(24018, '11/30/2017', 3013, 0, 2, 8, 52, 17, 0),
(25176, '11/30/2017', 3013, 0, 200, 15, 59, 20, 1),
(25176, '11/30/2017', 3013, 1, 200, 20, 30, 0, 05),
(25180, '11/30/2017', 3013, 0, 1, 21, 0, 5, 0),
(25187, '11/30/2017', 3013, 0, 1, 10, 0, 16, 6),
(35189, '11/30/2017', 3013, 0, 1, 16, 58, 2, 4)

  1. The system automatically clocks everyone out at 5:00 am and clocks them back in. The shift after 5:00 am goes on the next business day. There may be an issue with them clocking in at 5:00am and out at 7:00am then back again at 10:00pm --> 5:00 am. Not sure if this can be handled or not, this is only an issue at 2 of my locations. This is to track time in fast food restaurants, we only have a couple where employees are in the building 24 hours a day.
6

When there are multiple shifts, someone clocks out for a required 30 minute lunch period and the shift happens to cross over midnight, the latest OutTime is not showing, only the OutTime before midnight. How can I get the correct Out time? The highest "EmployeeShiftNumber" would be the last clock-out of the shift. Usually, "1" is the highest shift number, but there are occasions when there are values of 2 or 3 for an employee.

drop table #Shift
CREATE TABLE #Shift(
FKEmployeeNumber int,
DateOfBusiness datetime,
FKStoreId int,
EmployeeShiftNumber int,
FKJobCodeId int,
InHour int,
InMinute int,
OutHour int,
OutMinute int)

insert into #Shift ( FKEmployeeNumber, DateOfBusiness, FKStoreId, EmployeeShiftNumber, FKJobCodeId, InHour, InMinute,OutHour,OutMinute)
values

(80248, '12/4/2017', 8522, 0, 609, 16, 27, 19, 24),
(80248, '12/4/2017', 8522, 1, 609, 19, 25, 2, 30),
(526044, '11/25/2017', 3013, 0, 2, 17, 0, 1, 46),
(526044, '11/26/2017', 3013, 0, 2, 17, 30, 1, 54),
(526044, '11/27/2017', 3013, 0, 2, 17, 20, 1, 47),
(529251, '11/30/2017', 3013, 0, 1, 15,4, 20, 0),
(529251, '11/30/2017', 3013, 1, 1, 20, 0, 23, 18),
(529582, '11/27/2017', 3013, 0, 1, 12, 2, 20, 0),
(529582, '11/27/2017', 3013, 1, 1, 20, 2, 20, 15),
(530029, '11/27/2017', 3013, 0, 1, 16, 45, 22, 42)

SELECT FKEmployeeNumber AS EmpID, DateOfBusiness AS Date,
RIGHT(CONVERT(varchar(30), DATEADD(MINUTE, InTime_Mins, 0), 100), 7) AS InTime,
RIGHT(CONVERT(varchar(30), DATEADD(MINUTE, OutTime_Mins, 0), 100), 7) AS OutTime,
CONVERT(varchar(5), DATEADD(MINUTE, MinsWorked, 0), 8) AS TimeWorked
FROM (
select sh.FKEmployeeNumber, sh.DateOfBusiness,
MIN(sh.InHour60+InMinute) AS InTime_Mins,
MAX(sh.OutHour60+OutMinute) AS OutTime_Mins,
SUM(((sh.outhour+case when sh.OutHour < sh.InHour then 24 else 0 end)60 + sh.outminute) -
(sh.inhour60 + sh.inminute)) AS MinsWorked
from #shift sh
group by sh.FKEmployeeNumber, sh.DateOfBusiness
) AS derived
order by FKEmployeeNumber, DateOfBusiness

In the example above employee 26047 should have an out time of 2:30 am, but it is showing 7:24 pm. The number of hours is correct, just not the displayed Out Time.

EmpID	Date	InTime	OutTime	TimeWorked
80248	2017-12-04 00:00:00.000	 4:27PM	 7:24PM	10:02
526044	2017-11-25 00:00:00.000	 5:00PM	 1:46AM	08:46
526044	2017-11-26 00:00:00.000	 5:30PM	 1:54AM	08:24
526044	2017-11-27 00:00:00.000	 5:20PM	 1:47AM	08:27
529251	2017-11-30 00:00:00.000	 3:04PM	11:18PM	08:14
529582	2017-11-27 00:00:00.000	12:02PM	 8:15PM	08:11
530029	2017-11-27 00:00:00.000	 4:45PM	10:42PM	05:57
7

That's one the main reasons I didn't use a "true" date/time data type in my calc, in case there were "day crossover" issues. Try this query instead:

SELECT FKEmployeeNumber AS EmpID, DateOfBusiness AS Date,
RIGHT(CONVERT(varchar(30), DATEADD(MINUTE, InTime_Mins, 0), 100), 7) AS InTime,
RIGHT(CONVERT(varchar(30), DATEADD(MINUTE, OutTime_Mins % 1440, 0), 100), 7) AS OutTime,
CONVERT(varchar(5), DATEADD(MINUTE, MinsWorked, 0), 8) AS TimeWorked
FROM (
select sh.FKEmployeeNumber, sh.DateOfBusiness,
MIN(sh.InHour*60+InMinute) AS InTime_Mins,
MAX((CASE WHEN sh.OutHour < 5 THEN 24 ELSE 0 END + sh.OutHour)*60+OutMinute) AS OutTime_Mins,
SUM(((sh.outhour+case when sh.OutHour < sh.InHour then 24 else 0 end)*60 + sh.outminute) -
(sh.inhour*60 + sh.inminute)) AS MinsWorked
from #shift sh
group by sh.FKEmployeeNumber, sh.DateOfBusiness
) AS derived
order by FKEmployeeNumber, DateOfBusiness
1 Like