Apply a recursive CTE on grouped table rows (SQL server 2005).
- by Evan V.
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!
