CODEWITHSUNDEEP
sqlsql-serversql-server-2008
DevProg
DevProg

Reputation: 3

Find Range Overlap SQL On Table

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:

  1. 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.

  2. There may be some sql function to handle this?

Upvotes: 0

Views: 66

Answers (1)

Matt
Matt

Reputation: 14341

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.

Upvotes: 3

Related Questions