How to "flatten" a table without using a cursor

Question

I am trying to figure out how to do the following in SQL Server:

I have several rows in a census and would like to group them by resident and status (for starters). I would like to group the consecutive records as one record showing the date range. If there is a gap in dates, I would like there to be an additional record. For example:

Resident	Date	Status
John Doe	Jan 1	In
John Doe	Jan 2	In
John Doe	Jan 3	Hospital
John Doe	Jan 4	Hospital
***** Note the gap - no Jan 5 record ******
John Doe	Jan 6	Hospital
John Doe	Jan 7	Hospital
Mary Jane	Jan 1	In
Mary Jane	Jan 2	In
Mary Jane	Jan 3	In

I would to get the following result:

Resident	Begin Date	End Date	Status
John Doe	Jan 1	Jan 2	In
John Doe	Jan 3	Jan 4	Hospital
John Doe	Jan 6	Jan 7	Hospital
Mary Jane	Jan 1	Jan 3	In

I considered using a simple group by, but it won't take into account the gap in dates. Formerly, I solved this problem by using a CURSOR, but I was wondering if I can tackle this without using one. I have a feeling the solution will involve using a window and/or a pivot function, but haven't had success so far...

Answer 1

This is the solution I came up with after doing some research on SO:

WITH census_rows AS 
(
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY ResidentID, StatusCode ORDER BY CensusDate) AS RN_Status,
        ROW_NUMBER() OVER (PARTITION BY ResidentID ORDER BY CensusDate) AS RN
    FROM [CensusTest]
)

SELECT ResidentID, StatusCode, MIN(CensusDate) AS BeginDate, MAX(CensusDate) AS EndDate
FROM census_rows
GROUP BY ResidentID, StatusCode, (RN_Status-RN), DATEDIFF(DAY, RN, CensusDate)