Query records that chain together based on date gaps

Question

I have a table in SQL Server 2012 with the below simplified schema and I need to define a query that grabs the most recent record and then returns the chain of records that have less than 30 days of a gap between them. Once there is a gap of more than 30 days the chain is broken and does not continue. Overlaps should continue the chain. Is there a way to solve this without resorting to something slow and recursive?

Schema

ID          (int)
Arrival     (datetime)
Departure   (datetime)

Sample data

ID Arrival       Departure
1 2009-04-19    2009-04-28 
2 2009-04-27    2009-05-21 
3 2009-06-11    2009-07-01 
4 2009-07-19    2009-07-24 
5 2004-04-19    2004-04-21 
6 2009-02-28    2009-03-05

Desired Query Result

ID Arrival       Departure
4 2009-07-19    2009-07-24 
3 2009-06-11    2009-07-01
2 2009-04-27    2009-05-21 
1 2009-04-19    2009-04-28 

Answer 1

The following solution (SQL Fiddle) uses the SQL Server 2012LAST_VALUE function:

SELECT
    MaxDiff.ID,
    MaxDiff.Arrival,
    MaxDiff.Departure
FROM 
(
    -- Maximum difference encountered so far
    SELECT *,
        MaxDiff = LAST_VALUE(Diff.Diff)  OVER (
            ORDER BY Diff.Departure DESC 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
    FROM 
    (
        -- Difference between departure and prior arrival
        SELECT *,
            Diff = 
                ISNULL(DATEDIFF(DAY, r.Departure,
                        LAST_VALUE(r.Arrival) OVER (
                            ORDER BY r.Departure DESC 
                            ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING)), 0)
        FROM dbo.Records AS r
    ) AS Diff
) AS MaxDiff
WHERE
    MaxDiff.MaxDiff <= 30
ORDER BY
    MaxDiff.Departure DESC;

Results:

╔════╦════════════╦════════════╗
║ ID ║  Arrival   ║ Departure  ║
╠════╬════════════╬════════════╣
║  4 ║ 2009-07-19 ║ 2009-07-24 ║
║  3 ║ 2009-06-11 ║ 2009-07-01 ║
║  2 ║ 2009-04-27 ║ 2009-05-21 ║
║  1 ║ 2009-04-19 ║ 2009-04-28 ║
╚════╩════════════╩════════════╝

Execution plan: