SQL query for Punch-IN and Punch-OUT

Question

I am trying to create a SQL query in SQL Server 2012 for a report and came up with the below query. This query uses the data in the one of the temp table (EmpName, EntryDate, EntryTime, TrnName, TrnCode).

The data is inconsistent and manipulate it. The below code uses multiple scenarios mentioned in level2 but I want to edit the below query to put NULL values for any missing punch (either Entrance or Exit) before final time calculation.

Below is the sample data.

EmpName    EventDate    EventTime           TrnName    TrnCode
A         2015-07-20    11:07:29.0000000    Entrance    0
A         2015-07-20    11:08:09.0000000    Exit        1
A         2015-07-20    21:13:27.0000000    Exit        1
A         2015-07-21    12:07:03.0000000    Entrance    0
A         2015-07-21    21:04:02.0000000    Exit        1
A         2015-07-22    11:48:06.0000000    Entrance    0
A         2015-07-22    13:57:58.0000000    Entrance    0
A         2015-07-22    13:37:15.0000000    Exit        1
A         2015-07-22    20:59:22.0000000    Exit        1
B         2016-06-20    23:03:33.0000000    Entrance    0
B         2016-06-21    02:36:38.0000000    Exit        1
B         2016-06-21    17:02:29.0000000    Entrance    0
B         2016-06-21    17:27:03.0000000    Entrance    0
B         2016-06-21    19:11:24.0000000    Exit        1
B         2016-06-21    19:24:41.0000000    Entrance    0
B         2016-06-21    23:35:25.0000000    Exit        1
B         2016-06-21    23:57:03.0000000    Entrance    0
B         2016-06-22    17:27:00.0000000    Exit        1
B         2016-06-22    17:42:01.0000000    Entrance    0
B         2016-06-22    19:37:43.0000000    Exit        1
B         2016-06-22    21:27:35.0000000    Entrance    0
B         2016-06-22    21:27:59.0000000    Exit        1
B         2016-06-22    21:45:47.0000000    Exit        1
B         2016-06-22    21:56:15.0000000    Entrance    0
B         2016-06-23    00:42:44.0000000    Exit        1
B         2016-06-23    01:03:06.0000000    Entrance    0
B         2016-06-23    02:47:18.0000000    Exit        1

I do not have any fixed shift timings for each employee and want to check if time difference between punches is more than threshold(14 hours) then treat OUT as missed punch or else calculate hours difference normally.

;WITH Level1 
 AS ( 
 SELECT 
 EmpName (or EmpID) 
,TrnName (Exit or Entrance)
,CAST(EventDate AS DATETIME) + CAST(EventTime AS DATETIME) AS EntryDateTime
,LAG (TrnName, 1, 'N/A') OVER ( PARTITION BY EmpName ORDER BY EntryDateTime) AS LastEvent
,LEAD(TrnName, 1, 'N/A') OVER ( PARTITION BY EmpName ORDER BY EventDateTime ) AS NextEvent 
 FROM #TempData 
),
Level2
AS ( 
 SELECT 
 EmpName 
,TrnName 
,EntryDateTime 
,LastEvent
,NextEvent 
FROM   Level1 
WHERE
    NOT ( TrnName = 'Entrance' AND NextEvent = 'Entrance' )
    AND NOT ( TrnName = 'Entrance' AND LastEvent = 'Entrance' )
    AND NOT ( TrnName = 'Exit' AND LastEvent = 'Exit' )
    AND NOT ( TrnName = 'Entrance' AND NextEvent = 'N/A' ) 
    AND NOT (TrnName = 'Exit' AND LastEvent = 'N/A' )
),
Level3
AS ( 
 SELECT 
 EmpName
,TrnName 
,EntryDateTime 
,DATEDIFF(second, EntryDateTime,LEAD(EntryDateTime) OVER ( PARTITION BY EmpName ORDER BY EntryDateTime )) AS Seconds 
FROM Level2 
)

 SELECT 
 EmpName 
,EntryDateTime 
,(EntryDateTime+convert(DateTime,TIMEFROMPARTS((Seconds%(3600*24)/3600), ((Seconds%(3600*24)%3600)/60), ((Seconds%(3600*24)%3600)%60),0, 0))) AS ExitDateTime
,Seconds
,TIMEFROMPARTS((Seconds%(3600*24)/3600), ((Seconds%(3600*24)%3600)/60), ((Seconds%(3600*24)%3600)%60),0, 0) AS WorkTime
FROM Level3 
WHERE TrnName = 'Entrance'

Expected Results:

Empname | EntryDateTime | ExitDateTime | Difference(Seconds) | Difference(hh:mm:ss)
A | 2015-07-20 11:07:29.000 | 2015-07-20 11:08:09.000 | 40   | 00:00:40
A | NULL                    | 2015-07-20 21:13:27.000 | NULL | NULL
A | 2015-07-21 12:07:03.000 | 2015-07-21 21:04:02.000 | 32219| 08:56:59
B | 2016-01-11 16:25:35.000 | 2016-01-11 17:59:42.000 | 5647 | 01:34:07
B | 2016-01-11 18:11:55.000 | 2016-01-11 20:18:48.000 | 7613 | 02:06:53
B | 2016-01-11 23:06:06.000 |                    NULL | NULL | NULL
B | 2016-01-12 00:29:17.000 |                    NULL | NULL | NULL
B | 2016-01-12 01:32:55.000 | 2016-01-12 01:39:54.000 | 419  | 00:06:59
B | 2016-06-21 23:57:03.000 | 2016-06-22 17:27:00.000 |62997 | 17:29:57

--Last record is not correct as In-Time is for last IN on 21-06-16 and Out-time is for First OUT on 22-06-16. In such case, OUT-Time value should be NULL and in next record IN-Time value should be NULL with OUT-time as 2016-06-22 17:27:00.000

For threshold limit requirement, i am referring something similar as the below [link] - http://stackoverflow.com/questions/32419833/how-to-calculate-the-night-working-shift-in-sql-server/39222189#39222189 However, here the user is updating Punch-In time with Punch-out for a particular which is incorrect, i am looking for NULL value wherever a Punch(In or Out) is missing. — ankur jain, Aug 30 '16 at 16:57
These are the requirements that i am trying to capture in this query: - regular shift punch in / out - Working - night shift punch in / out - Working - regular shift with missing punch out - Not Working - regular shift with missing punch in - Not Working - night shift with missing punch out - Not Working - night shift with missing punch in - Not Working - calculate time difference between each Punch in and out - Working @TabAlleman — ankur jain, Aug 30 '16 at 17:10
@ankurjain ah ok. I thought IN and OUT was referring to SQL keywords. Could you please add the expected result to your question and format it? Otherwise it's hard to read. — Verena Haunschmid, Aug 30 '16 at 17:35

SQL query for Punch-IN and Punch-OUT

0 Answers0