CODEWITHSUNDEEP
sql-servert-sqlnumbersrange
Irresistance
Irresistance

Reputation: 157

MS-SQL - Select Non-overlapping integer ranges

I have a table containing a bunch of number ranges, something like:

ID START END
1 1200 1500
2 1450 1700
3 1800 2100
4 2500 3000
5 2900 3300

What I want to be able to do is select only the non-overlapping ranges from this set (so, for example 1,3 and 4, or 1,3 and 5). In addition, I must be able to specify an additional 'padding' value (like a break) that can be variable. So for instance, after selecting ID=1, I may wish to add 400 to end value (going from 1500 to 1900), thus making ID=3 also not available if ID=1 is part of the set of selected ranges.

I've had a look at no less than 7-9 SO posts and they all seem fairly close to what I need, but not quite that. I found one that can find the overlapping ones:

SELECT *
FROM TEMP_Times a
JOIN TEMP_Times b on a.TimeStart <= b.TimeEnd
and a.TimeEnd >= b.TimeStart
and a.TimeID <> b.TimeID;

But I am failing into converting this into what I need instead. If recursion will be needed that is fine; at most I'll have to grab 10-12 records at a time.

Upvotes: 0

Views: 1464

Answers (1)

Mikhail Lobanov
Mikhail Lobanov

Reputation: 3026

I think NOT EXISTS can simplify your query:

SELECT *
FROM @Test a
WHERE NOT EXISTS(
    SELECT 1
    FROM @Test b
    WHERE a.TimeStart <= b.TimeEnd AND a.TimeEnd >= b.TimeStart 
        AND a.TimeStart >= b.TimeStart
        AND a.TimeID <> b.TimeID
)

Solution based on window function:

WITH cte AS(
    SELECT *,
        MaxEnd = MAX(TimeEnd) OVER (ORDER BY TimeStart ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)
    FROM @Test
)
SELECT * 
FROM cte 
WHERE MaxEnd IS NULL OR TimeStart > MaxEnd

Upvotes: 1

Related Questions