Time difference between swipe-in and swipe-out for employees; allow for tailgating and exit on different day

This is continuation of/added complexity to the question: sql query – Get the difference time between swipe in – Swipe out for employee. We have similar swipe-in/out data, but additional constraints.

Sample source data:

EMPID EVENT_TYPE Swipe_time
1     EN1        2012-06-01 12:00
1     EX         2012-06-01 12:30
2     EN1        2012-06-01 12:50
1     EN2        2012-06-01 13:10
1     EN2        2012-06-01 15:50
2     EX         2012-06-01 13:30
2     EN1        2012-06-01 14:00
2     EX         2012-06-02 19:00
1     EX         2012-06-02 19:30

The data comes in the above format but the desired output and input data increase the complexity.

Points to be considered are :

1. There are two entry codes : EN1 , EN2 – representing two different offices , However both offices record exit with same code EX. Time spent at both offices account to the in time of the employee.

2. There are cases where an employee can tailgate hence exit record corresponding to an entry might be missing (for example consider Row # 4 and 5 in above given sample data ), in those cases we have to highlight that row saying exit is missing/ invalid under VALIDITY column of output.

3. An employee can enter the office premise on day 1 and exit it on day 2 (for example consider Row #8,9 in above given sample data) for such cases we should have 2 records/rows in the output: 1st row for day1 : entry time is swipe_time of event_type:’EN1 or EN2′ , exit time: 23:59 2nd row for day2 : entry time:00:00 , exit time is the swipe_time of event_type:’EX’

4. Every record should have two fields DAY_HOURS CALENDER_HOURS. If an employee enters at 18:00 on day1 and exits at 3:00 on day2 , DAY_HOURS = 8 hours(complete hours of work) and CALENDER_HOURS = 6 hours (hours on that particular day)

Required Output: Table in below format:

EMPID EVENT_TYPE TIME_IN TIME_OUT DAY_HOURS CALENDER_HOURS VALIDITY

Answer

With help of a friend , I solved the issue mentioned in Point # 2: by below steps ,As I wanted the solution only in sql script , with my little sql knowledge I designed the solution in 3 steps, I am sure it can be handled in a simpler way by experts :

Considering base table as EMP_DATA , I used one temp table : Temp_1 For base table I have added a “VALIDITY” field to flag the records at the final step.

EMP_DATA:

EMPID EVENT_TYPE Swipe_time       VALIDITY
1     EN1        2012-06-01 12:00
1     EX         2012-06-01 12:30
2     EN1        2012-06-01 12:50
1     EN2        2012-06-01 13:10
1     EN2        2012-06-01 15:50
2     EX         2012-06-01 13:30
2     EN1        2012-06-01 14:00
2     EX         2012-06-02 19:00
1     EX         2012-06-02 19:30

Step1:

below query will assign the rownumbers for rows in EMP_DATA and copy them to temp_1 table:

create table Temp_1 (EMP_ID int, Event_Type char(10), Swipe_Time datetime, rownum int, Lastevent char(10), Nextevent char(10)) 

Insert into  Temp_1             
SELECT  EMP_ID, EVENT_TYPE,
ROW_NUMBER() OVER ( PARTITION BY EMP_ID 
ORDER BY SWIPE_TIME ) AS RowNumber      
    FROM EMP_DATA

Step 1 Output:

 EMPID EVENT_TYPE Swipe_time         RowNumber  LastEvent  NextEvent
1     EN1        2012-06-01 12:00    1
1     EX         2012-06-01 12:30    2
2     EN1        2012-06-01 12:50    1
1     EN2        2012-06-01 13:10    3
1     EN2        2012-06-01 15:50    4
2     EX         2012-06-01 13:30    2
2     EN1        2012-06-01 14:00    3
2     EX         2012-06-02 19:00    4
1     EX         2012-06-02 19:30    5

Step2:

identify last event (previous event ) and next event for each event in the row of the temp_1 table

 Update  main set main.LastEvent =yy.LEvent,main.NextEvent=yy.NEvent
 from TEMP_1 main
 inner join
 ( SELECT   A.EMP_ID , A.EVENT_TYPE, A.SWIPE_TIME, A.ROWNUMBER                                                                                                                                                                                                                                                          
               COALESCE(LastVal.ZONE_NAME, 'N/A') AS LEvent ,                                                                                                                                                                                                                                                               
                   COALESCE(NextVal1.ZONE_NAME, 'N/A') AS NEvent                                                                                                                                                                                                                                                       
          FROM Temp_1  A                                                                                                                                                                                                                                                               
               LEFT JOIN Temp_1 LastVal                                                                                                                                                                                                                                                        
                  ON A.EMP_ID= LastVal.EMP_ID
                     AND A.ROWNUMBER - 1 = LastVal.ROWNUMBER                                                                                                                                                                                                                                                   
               LEFT JOIN Temp_1 NextVal1                                                                                                                                                                                                                                                    
                  ON A.EMP_ID= NextVal1.EMP_ID
                     AND A.ROWNUMBER + 1 = NextVal1.ROWNUMBER)yy                                       
on main.EMP_ID=yy.EMP_ID,
and main.event_type=yy.event_type
and main.swipe_time = yy.swipe_event

Step 2 Output:

EMPID EVENT_TYPE Swipe_time         RowNumber  LastEvent  NextEvent
1     EN1        2012-06-01 12:00    1           N/A        EX
1     EX         2012-06-01 12:30    2           EN1        EN2
2     EN1        2012-06-01 12:50    1           N/A        EX
1     EN2        2012-06-01 13:10    3           EX         EN2
1     EN2        2012-06-01 15:50    4           EN2        EX
2     EX         2012-06-01 13:30    2           EN1        EN1
2     EN1        2012-06-01 14:00    3           EX         EX
2     EX         2012-06-02 19:00    4           EN1        N/A
1     EX         2012-06-02 19:30    5           EN2        N/A

Step 3:

Based on the LastEvent and NextEvent we can identify the tailgated entries and update the main table EMP_DATA by below query :

 Update emp set emp.VALIDITY='N'
from EMP_DATA
INNER JOIN
(select *
from TEMP_1
where (EVENT_TYPE = 'EX' and LastEvent = 'EX') 
or (EVENT_TYPE IN ('EN1','EN2') and NextEvent  IN ('EN1','EN2')))yy
ON emp.EMP_ID = yy.EMP_ID
and emp.EVENT_TYPE = yy.EMP_ID
and emp.Swipe_time = yy.Swipe_time

Final Output: *Step 3 Output:*

EMPID EVENT_TYPE Swipe_time       VALIDITY
1     EN1        2012-06-01 12:00
1     EX         2012-06-01 12:30
2     EN1        2012-06-01 12:50
1     EN2        2012-06-01 13:10    N
1     EN2        2012-06-01 15:50    
2     EX         2012-06-01 13:30
2     EN1        2012-06-01 14:00
2     EX         2012-06-02 19:00
1     EX         2012-06-02 19:30