Time Clock Management

Transact-SQL
1

I found some good logic on this site which calculates clock in vs clock out times. It even included over midnight scenarios where an employee punches out the next day. The code below handles this:

sum(case when TimeType='IN' then -1 * (datepart(hh,DayWorked) + datepart(mi,DayWorked) /60.0)
else datepart(hh,DayWorked) + datepart(mi,DayWorked)/60.0 end) TotMTCHrs, crew
from #TEMP_NEW_TIMESHEET, Employee EE
Where #TEMP_NEW_TIMESHEET.EmployeeId = EE.Id
group by EmployeeId, convert(varchar(10),DayWorked,101),Firstname, Lastname, crew ) t

along with: cast(Round(case when TotMTCHrs < 0 then 24 + TotMTCHrs else TotMTCHrs end,2) as decimal(16,2)) TotMTCHrs
Which accounts for over midnight.

Now a new situation has entered the picture.
I need to be sure that there is always a clock in and clock out pair. If not then the lone Clock In or Clock Out entry should be ignored.
I am not sure how to incorporate this.

Example 1
Person clocks In at 9:00pm and clocks out at 4:00 am the next day. The query will write two entries: the first is the time between 9:00pm and Midnight and the second between Midnight and 4:00am.
This works great!

Example 2
Person clocks in at 8:00am then clocks out at 5:00pm- it creates a single record showing 9 hours. This works great!

Example 3
An employee Clocks Out at 4:00am, but there was no matching Clock in from that day or the day before, it writes a record from Midnight to 4:00am (4 hours), when it should ignore this clock out because there is no matching Clock In.
.
Pairs should be configured by an employeeId and a CrewId. So in Example 3 for this particular employee there was no clock In under this CrewId. So no transaction should have been written from Midnight to 4:00am.

Can you help!? Thanks for your time.

2

First, welcome to the forum!

Second, please post directly usable data, including data that covers as many "edge"/difficult examples as you can reasonably think of and do. So, post actual sample data for Examples 1-3, with a comment about what it is, something like:

CREATE TABLE #TEMP_NEW_TIMESHEET ( EmployeeId int NOT NULL, TimeType char(3) NOT NULL, DayWorked datetime NOT NULL, CrewId int NOT NULL ) /* ,...*/
INSERT INTO #TEMP_NEW_TIMESHEET VALUES
(1, 'IN', '20190123 21:00', 100), /*working from 9PM to ...*/
(1, 'OUT', '20190124 04:00', 100), /* 4AM next day, works OK now */

....

Which version of SQL? If 2012 or later, LAG could be a big help here.

3

Thanks Scott,
Here is sample data:
EmpId DayWorked TimeType Crew
112038 2019-03-11 06:30:00.000 IN 444162
112038 2019-03-11 07:00:00.000 OUT 444162
112038 2019-03-11 14:30:00.000 OUT 444162

The first two entries are correct, they clocked in and out with 30 minutes.
The third entry is incorrect, it is a clock out that has no clock in.
There are no records for this person for the previous day 03/10 either, so this is an invalid entry and should not be inserted. Right now it is putting out a record of 14:30 hours (from midnight to clock out time)
so part two of query adds the :30 minutes to the 14:30 hours minutes for a total of 15 hours, when it should only be :30 minutes.

CREATE TABLE #TEMP_NEW_TIMESHEET (EmployeeId varchar(10), DayWorked DateTime, TimeType char(3), Crew char(6))

INSERT INTO #TEMP_NEW_TIMESHEET (EmployeeId, DayWorked, TimeType,Crew)
Select Employeeid Employee,
CONVERT(varchar(15),CAST(Time AS date),101) + ' ' + CONVERT(varchar(15),CAST(Time AS Time),100) DayWorked,
Case when isclockin = 1 then 'IN'
Else 'OUT'
End as TimeType, CrewId
From Timesheet
where (CONVERT(DATE,[Time]) >= (DATEADD(day, -6, @WeekBeginDate)) and convert(Date, [Time]) <= @WeekEndDate)
Order by employeeId

-- show results of step
Select * from #TEMP_NEW_TIMESHEET order by EmployeeId, dayworked;

EmpId. DayWorked................ TimeType Crew
112038 2019-03-11 06:30:00.000 IN 444162
112038 2019-03-11 07:00:00.000 OUT 444162
112038 2019-03-11 14:30:00.000 OUT 444162

-- STEP 2 USING TEMP TABLE, BUILD ANOTHER TEMP TABLE THAT BREAKDOWN WORKDAY HOURS INTO INDIVIDUAL DAYS WITH TOTAL HOURS FOR THAT DAY
CREATE TABLE #Temp_Table_With_Calculated_Daily_Hours
(EmployeeId varchar(10), Firstname varchar(25), LastName varchar(25), DayWorked Date, WeekEndDate Date, Crew char(6), TotMTCHrs decimal(8,2), PunchMinutes int, FromQuery char(3))

INSERT INTO #Temp_Table_With_Calculated_Daily_Hours
(EmployeeId, Firstname, LastName, DayWorked, WeekEndDate, Crew, TotMTCHrs, PunchMinutes, FromQuery)

Select EmployeeId EmployeeID, Firstname, LastName, Dayworked, pc.periodend as WeekEndDate, Crew,

cast(Round(case when TotMTCHrs < 0 then 24 + TotMTCHrs else TotMTCHrs end,2) as decimal(16,2)) TotMTCHrs,

Cast(cast(Round(case when TotMTCHrs < 0 then 24 + TotMTCHrs else TotMTCHrs end,2) as decimal(16,2)) * 60 as int) PunchMinutes,
'MTC' FromQuery

FROM
(select EmployeeId, EE.FirstName Firstname, EE.Lastname LastName, convert(varchar(10),DayWorked,101) DayWorked,
sum(case when TimeType='IN' then -1 * (datepart(hh,DayWorked) + datepart(mi,DayWorked) /60.0)
else datepart(hh,DayWorked) + datepart(mi,DayWorked)/60.0 end) TotMTCHrs,
crew
from #TEMP_NEW_TIMESHEET, Employee EE
Where #TEMP_NEW_TIMESHEET.EmployeeId = EE.Id
group by EmployeeId, convert(varchar(10),DayWorked,101),Firstname, Lastname, crew ) tt
join [AsplundhDownload].[dbo].[PERIODEND_CALENDAR] PC
on PC.PERIODEND >= CONVERT(char(10),DayWorked, 112) and PC.PERIODEND < DATEADD(DD,7,CONVERT(char(10),DayWorked, 112))

-- show results from step
Select * from #Temp_Table_With_Calculated_Daily_Hours order by EmployeeId;

EmpId- FirstName-LastName -DayWorked - WeekEndDate - Crew - TotMTCHours - PunchMinutes - FromQuery
112038 FIRSTNAME LASTNAME 2019-03-11 2019-03-16 444162 15.00 900 MTC

4

Ok, getting there, but ...

INSERT INTO #TEMP_NEW_TIMESHEET ( ... )
Select ...
From Timesheet

I/we don't have access to "Timesheet". Responders here need the actual data, typically in VALUES clause, as in my sample above.

Btw, do you know the version of SQL? SQL 2008? 2012? 2016?

5

SQL Server 2014.
The timesheet data is a very long record. There are many fields that wont apply to this let me get some information together and I will post. thanks

6

EmployeeID modified for privacy.

Emp Id Time .....................................................IsClockIn IsClockOut CrewId
012038 2019-03-11 06:30:00.0000000 -05:00....1...................... 0............. 444162
012038 2019-03-11 07:00:00.0000000 -05:00....1...................... 0.............444498
012038 2019-03-11 07:00:00.0000000 -05:00.... 0......................1.............444162
012038 2019-03-11 14:30:00.0000000 -05:00.... 0......................1.............444162
012038 2019-03-11 14:30:00.0000000 -05:00.....1..................... 0.............444498
012038 2019-03-12 06:40:00.0000000 -05:00.....1..................... 0.............444622
012038 2019-03-12 06:40:00.0000000 -05:00.... 0......................1............444498
012038 2019-03-12 14:30:00.0000000 -05:00.... 0......................1............444622
012038 2019-03-13 06:35:00.0000000 -05:00.....1..................... 0............444263
012038 2019-03-13 14:25:00.0000000 -05:00.... 0......................1............444263
012038 2019-03-14 06:30:00.0000000 -05:00.....1..................... 0.............444263

7

Well, I'm very busy at work, it's gonna be a while before I can convert this into actual, usable data myself. And I still have no idea which version of SQL I'm writing for.

You may want to re-post the q and see if someone else has time to do the formatting and deal with possible differences in the specific SQL version they're using vs the specific SQL version you're using.

8

ok. further up on reply's I posted Sql Server 2014.

9

IF OBJECT_ID('tempdb..#TEMP_NEW_TIMESHEET') IS NOT NULL
DROP TABLE #TEMP_NEW_TIMESHEET

CREATE TABLE #TEMP_NEW_TIMESHEET ( EmployeeId int NOT NULL, TimeType char(3) NOT NULL, DayWorked datetime NOT NULL, CrewId int NOT NULL ) /* ,.../
INSERT INTO #TEMP_NEW_TIMESHEET VALUES
(1, 'IN', '20190123 21:00', 100), /working from 9PM to .../
(1, 'OUT', '20190124 04:00', 100), / 4AM next day, works OK now */
(1, 'IN', '20190125 21:00', 100),
(1, 'OUT', '20190125 23:30', 100),
(1, 'OUT', '20190125 05:00', 100);

WITH CTE
AS (SELECT A.EmployeeId,
A.DayWorked AS StartTime,
Z.DayWorked AS EndTime
FROM #TEMP_NEW_TIMESHEET A
CROSS APPLY
(
SELECT TOP 1 *
FROM #TEMP_NEW_TIMESHEET B
WHERE A.EmployeeId = B.EmployeeId
AND A.DayWorked < B.DayWorked
ORDER BY B.DayWorked
) Z
WHERE A.TimeType = 'IN'
AND Z.TimeType = 'OUT'),
SPLIT
AS (SELECT EmployeeId,
StartTime,
EndTime
FROM CTE
WHERE CAST(StartTime AS DATE) = CAST(EndTime AS DATE)
UNION ALL
SELECT EmployeeId,StartTime,CAST(DATEADD(DD, 1, StartTime) AS DATE) AS EndTime
FROM CTE
WHERE CAST(StartTime AS DATE) <> CAST(EndTime AS DATE)
UNION ALL
SELECT EmployeeId,CAST(EndTime AS DATE) AS StartTime,EndTime
FROM CTE
WHERE CAST(StartTime AS DATE) <> CAST(EndTime AS DATE))
SELECT EmployeeId,CAST(SPLIT.StartTime AS DATE) AS DateOfInterest,DATEDIFF(MINUTE,SPLIT.StartTime,SPLIT.EndTime)/60.0 AS TimeWorkedHours
FROM SPLIT;

1 Like
10

Thanks Lewie,
I need to see how this will fit in with rest of procedure.
It brought back a single .50 record (as you know). This calculation is already being done down further in the procedure. I was hoping to just get back:

EmpId...... DayWorked............ TimeType Crew
112038 2019-03-11 06:30:00.000 IN 444162
112038 2019-03-11 07:00:00.000 OUT 444162

11

After hours....days of trying to figure this out, it was suggested using a cursor to align clock in's with clock out's and if there are two clock in's together or two clock outs, toss one of them as they are considered invalid. This helped a great deal in matching up valid pairs. This gave me the ability to look back at the previous entry to determine what kind of clock activity it was and is the current entry a matching pair. Thanks for all of your help.

12

cursors perform badly
IF OBJECT_ID('tempdb..#TEMP_NEW_TIMESHEET') IS NOT NULL
DROP TABLE #TEMP_NEW_TIMESHEET

CREATE TABLE #TEMP_NEW_TIMESHEET ( EmployeeId INT NOT NULL, TimeType CHAR(3) NOT NULL, DayWorked DATETIME NOT NULL, CrewId INT NOT NULL )
INSERT INTO #TEMP_NEW_TIMESHEET VALUES
(1, 'IN', '20190123 21:00', 100),
(1, 'OUT', '20190124 04:00', 100),
(1, 'IN', '20190125 21:00', 100),
(1, 'OUT', '20190125 23:30', 100),
(1, 'OUT', '20190125 05:00', 100);

WITH CTE
AS (SELECT A.EmployeeId,
A.DayWorked AS StartTime,
Z.DayWorked AS EndTime,
A.CrewId
FROM #TEMP_NEW_TIMESHEET A
CROSS APPLY
(
SELECT TOP 1
*
FROM #TEMP_NEW_TIMESHEET B
WHERE A.EmployeeId = B.EmployeeId
AND A.DayWorked < B.DayWorked
ORDER BY B.DayWorked
) Z
WHERE A.TimeType = 'IN'
AND Z.TimeType = 'OUT')
SELECT EmployeeId,StartTime AS Dayworked,'IN' AS TimeType,CrewID
FROM CTE
UNION ALL
SELECT EmployeeId,EndTime,'OUT',CrewID
FROM CTE;