Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 
share|improve this question
add comment

1 Answer

up vote 6 down vote accepted

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:

Plan 1

share|improve this answer
    
This solution is very close to what I am looking for, the only issue is if you add (7,'20040422','20040428') to the table it will include the record even though it is not in the chain. I should have given better test cases. –  JustinDoesWork Feb 22 '13 at 16:15
    
@JustinDoesWork Added that extra record into the example and updated the code. –  Paul White Feb 25 '13 at 2:05
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.