Implementing recursive Common Table Expression for grouped data in SQL Server 2005

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.

Wait, something’s off with the sequencing logic. Employee 5 in area 12 shows sequence 1 but not 2 in your expected output - why? Am I missing a gap condition? And what counts as “sequential” here - just consecutive numbers or does entry/exit timing matter too?

Your performance problem is happening because the recursive CTE is trying to process all groups at once without proper indexing. Here’s what I’d do: add ROW_NUMBER() to create separate processing paths for each EMPID-AREAID combo. Start with a base query that finds the starting points for each group, then modify the recursive part to include a group identifier. You’ll want composite indexes on (EMPID, AREAID, SEQUENCE) and (EMPID, AREAID, EXIT_TIME, ENTRY_TIME) for the joins. Since you’re on SQL Server 2005, try using a table variable to store intermediate results for each group before combining them - this cuts down the recursive overhead big time when you’re dealing with multiple employee-area combinations.

Your join condition looks backwards - shouldn’t it be AccessCTE.EXIT_TIME = AL.ENTRY_TIME? Try breaking this down with temp tables first to find the bottleneck. Create a temp table with just the empid/areaid groups you need, then run the recursive CTE on smaller chunks.