patternsqlMinor
Query records that chain together based on date gaps
Viewed 0 times
queryrecordstogetherdatethatbasedgapschain
Problem
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
Sample data
Desired Query Result
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-05Desired 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-28Solution
The following solution (SQL Fiddle) uses the SQL Server 2012
Results:
Execution plan:
LAST_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:
Code Snippets
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;╔════╦════════════╦════════════╗
║ 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 ║
╚════╩════════════╩════════════╝Context
StackExchange Database Administrators Q#35204, answer score: 7
Revisions (0)
No revisions yet.