I’m working with a database table called ACCESSLOG that tracks when employees enter and exit different areas. The table is organized by EMPID and AREAID and contains these columns:
EMPID | AREAID | ENTRY_TIME | EXIT_TIME | SEQUENCE
------------------------------------------------------------------
5 | 12 | 15-5-2010 09:30 | 15-5-2010 10:30 | 1
5 | 12 | 15-5-2010 07:45 | 15-5-2010 08:45 | 2
5 | 3 | 15-5-2010 16:15 | 15-5-2010 17:15 | 1
5 | 3 | 15-5-2010 15:15 | 15-5-2010 16:15 | 2
5 | 3 | 15-5-2010 14:15 | 15-5-2010 15:15 | 3
21 | 7 | 15-5-2010 12:00 | 15-5-2010 13:00 | 1
21 | 7 | 15-5-2010 12:00 | 15-5-2010 13:00 | 2
I need to filter out only the sequential records for each employee and area combination. My expected output should be:
EMPID | AREAID | ENTRY_TIME | EXIT_TIME | SEQUENCE
------------------------------------------------------------------
5 | 12 | 15-5-2010 09:30 | 15-5-2010 10:30 | 1
5 | 3 | 15-5-2010 16:15 | 15-5-2010 17:15 | 1
5 | 3 | 15-5-2010 15:15 | 15-5-2010 16:15 | 2
5 | 3 | 15-5-2010 14:15 | 15-5-2010 15:15 | 3
21 | 7 | 15-5-2010 12:00 | 15-5-2010 13:00 | 1
I tried using a recursive CTE approach to avoid cursors:
WITH AccessCTE (EMPID, AREAID, ENTRY_TIME, EXIT_TIME, SEQUENCE)
AS (
SELECT AL.EMPID,
AL.AREAID,
AL.ENTRY_TIME,
AL.EXIT_TIME,
AL.SEQUENCE
FROM ACCESSLOG AL
WHERE AL.SEQUENCE = 1
UNION ALL
SELECT AL.EMPID,
AL.AREAID,
AL.ENTRY_TIME,
AL.EXIT_TIME,
AL.SEQUENCE
FROM ACCESSLOG AL
INNER JOIN AccessCTE ON AL.SEQUENCE = AccessCTE.SEQUENCE + 1
WHERE AccessCTE.ENTRY_TIME = AL.EXIT_TIME
AND AccessCTE.EMPID = AL.EMPID
AND AccessCTE.AREAID = AL.AREAID
)
This query works fine with small datasets but becomes really slow with larger ones. I think I need to apply the recursive CTE to each EMPID and AREAID group separately but I’m not sure how to implement this properly. Any suggestions would be helpful.