Apply a recursive CTE on grouped table rows (SQL server 2005).

Posted by Evan V. on Stack Overflow See other posts from Stack Overflow or by Evan V.
Published on 2010-04-29T13:30:22Z Indexed on 2010/04/29 13:37 UTC
Read the original article Hit count: 400

Filed under:

sql-server-2005

|

recursive-query

Hi all,

I have a table (ROOMUSAGE) containing the times people check in and out of rooms grouped by PERSONKEY and ROOMKEY. It looks like this:

PERSONKEY | ROOMKEY | CHECKIN           | CHECKOUT         | ROW
----------------------------------------------------------------
1         | 8       |  13-4-2010 10:00  | 13-4-2010 11:00  | 1
1         | 8       |  13-4-2010 08:00  | 13-4-2010 09:00  | 2

1         | 1       |  13-4-2010 15:00  | 13-4-2010 16:00  | 1
1         | 1       |  13-4-2010 14:00  | 13-4-2010 15:00  | 2
1         | 1       |  13-4-2010 13:00  | 13-4-2010 14:00  | 3

13        | 2       |  13-4-2010 15:00  | 13-4-2010 16:00  | 1
13        | 2       |  13-4-2010 15:00  | 13-4-2010 16:00  | 2

I want to select just the consecutive rows for each PERSONKEY, ROOMKEY grouping. So the desired resulting table is:

PERSONKEY | ROOMKEY | CHECKIN           | CHECKOUT         | ROW
----------------------------------------------------------------
1         | 8       |  13-4-2010 10:00  | 13-4-2010 11:00  | 1

1         | 1       |  13-4-2010 15:00  | 13-4-2010 16:00  | 1
1         | 1       |  13-4-2010 14:00  | 13-4-2010 15:00  | 2
1         | 1       |  13-4-2010 13:00  | 13-4-2010 14:00  | 3

13        | 2       |  13-4-2010 15:00  | 13-4-2010 16:00  | 1

I want to avoid using cursors so I thought I would use a recursive CTE. Here is what I came up with:

;with CTE (PERSONKEY, ROOMKEY, CHECKIN, CHECKOUT, ROW)
as (select RU.PERSONKEY,
           RU.ROOMKEY, 
           RU.CHECKIN,
           RU.CHECKOUT,
           RU.ROW
    from ROOMUSAGE RU
    where RU.ROW = 1

    union all

    select RU.PERSONKEY,
           RU.ROOMKEY, 
           RU.CHECKIN,
           RU.CHECKOUT,
           RU.ROW
    from ROOMUSAGE RU inner join CTE on RU.ROWNUM = CTE.ROWNUM + 1
    where CTE.CHECKIN = RU.CHECKOUT
      and CTE.PERSONKEY = RU.PERSONKEY
      and CTE.ROOMKEY = RU.ROOMKEY)

This worked OK for very small datasets (under 100 records) but it's unusable on large datasets.

I'm thinking that I should somehow apply the cte recursevely on each PERSONKEY, ROOMKEY grouping on my ROOMUSAGE table but I am not sure how to do that.

Any help would be much appreciated,

Cheers!

Developer IT