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 :
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.
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.
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’
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
Advertisement
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