Find Range Overlap SQL On Table

Question

I have a table like below:

ID....LClass....UClass

1..........1............10

2..........8............20

3..........21..........30

How can I determine that the ranges for ID 1 and ID 2 overlap? I have thought of:

selecting each value into a variable and find out if it is between any of the ranges. This will be difficult as the actual table is quite large.
There may be some sql function to handle this?

See [Overlapping data](https://stackoverflow.com/questions/12667726/overlapping-data) and [Determine whether two date ranges overlap](http://stackoverflow.com/questions/325933/determine-whether-two-date-ranges-overlap/), for starters. — Jonathan Leffler, Jun 14 '16 at 17:19

score 3 · Accepted Answer · answered Jun 14 '16 at 17:02

DECLARE @Table AS TABLE (Id INT, LClass INT, UClass INT)
INSERT INTO @Table (Id, LClass, UClass)
    VALUES (1,1,10),(2,8,20),(3,21,30)

SELECT *
FROm
    @Table t1
    INNER JOIN @Table t2
    ON t1.Id <> t2.Id
    AND (t1.LClass BETWEEN t2.LClass AND t2.UClass
   OR t1.UClass BETWEEN t2.LClass AND t2.UClass)

Use can use a self join on the table and search for values between the other table. You will want to exclude the self referencing record.

Find Range Overlap SQL On Table

1 Answers1