Jeff doesn't like it when I use

SELECT $

In my tally tables. :hehe: Just having a little fun there.

BTW. I noticed you also wanted the open-ended gaps before start and after end. From your comments in the code you posted:

--======= I want a final dataset as follows:

/*

location_idunoccupied_start_dtunoccupied_end_dttransaction_type

1 NULL 2013-07-01 12:38 unoccupied

1 2013-07-05 08:232013-07-07 14:35unoccupied

1 2013-07-10 09:39NULL unoccupied

2 NULL 2013-07-01 16:31 unoccupied

2 2013-07-02 14:312013-07-03 13:37unoccupied

2 2013-07-05 22:272013-07-08 19:32unoccupied

2 2013-07-11 04:562013-07-15 18:38unoccupied

2 2013-07-18 14:44NULL unoccupied

3 NULL 2013-07-12 01:32 unoccupied

3 2013-07-12 21:19NULL unoccupied

Those are easy enough to add by tacking the following onto the end of the code I posted (after the HAVING clause).

UNION ALL

SELECT location_id, NULL, MIN(start_dt)

FROM #stays

GROUP BY location_id

UNION ALL

SELECT location_id, MAX(end_dt), NULL

FROM #stays

GROUP BY location_id

ORDER BY location_id, StartDate, EndDate

I got so focused on the gaps thingy that I missed that little wrinkle on first scan.