title: SQL query for Punch-IN and Punch-OUT
body: <p>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 (<code>EmpName, EntryDate, EntryTime, TrnName, TrnCode</code>). </p>
  
  <p>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. </p>
  
  <p>Below is the sample data.</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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.</p>
  
  <pre><code>;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'
  </code></pre>
  
  <p>Expected Results:</p>
  
  <pre><code>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 
  </code></pre>
  
  <p>--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</p>