MS-SQL - Select Non-overlapping integer ranges

Question

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.

It's not clear to me what the expected results of such a query would be. As you noted, both (1,3,4) and (1,3,5) satisfy the conditions you've laid out. So do (2,3,4), (2,3,5), (1,3), (1,4), (1,5), (2,3), (2,4), (2,5), (3,4), and (3,5). Any single row also works. What would make any one of these solutions better than the others? — Joe Farrell
– Joe Farrell, Commented May 5, 2017 at 15:11
The expected results are needed to get a list of to-from times (the values are integers, but only for speedier processing - they represent a date and a time) that I can book equipment in to.The break is sometimes needed to repair or refurbish a given item. But what I get is a long list of from-until values which to a large degree overlap; sometimes a few minutes, sometimes hours) and so I need to filter out a list that is fully 'clean'. — Irresistance
– Irresistance, Commented May 5, 2017 at 17:04

Mikhail Lobanov · Accepted Answer · 2017-05-05 14:54:46Z

1

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

edited May 5, 2017 at 14:54

answered May 5, 2017 at 14:23

Mikhail Lobanov

3,02612 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

TriV Over a year ago

I don't think it's an correct answer. For example, I add a new row (6,1,1000000), you could imagine your result.

Mikhail Lobanov Over a year ago

It will return single row (6,1,1000000). What's wrong? If you sort by TimeStart it will be a first value

TriV Over a year ago

In this case 3 rows with id 1,3,4 is still an expected result.

Mikhail Lobanov Over a year ago

It depends sort order when you selecting ranges. You assume this is TimeID, but I assume it's a TimeStart. however it's not important: a.TimeStart >= b.TimeStartcan be replaced with a.TimeID >= b.TimeID

Mikhail Lobanov Over a year ago

In my second solution this is specified explicitly: OVER (ORDER BY TimeStart (You can write OVER (ORDER BY TimeID to receive result that @TriV expects)

|

Collectives™ on Stack Overflow

MS-SQL - Select Non-overlapping integer ranges

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related