Query to check overlapping ranges in sql server?

Question

I have my table as

   From_Range      ToRange
   1                999
   9000             10000
   2000             5000

When I try to insert the range values 1000 - 3000 it should fail since some values within this new range fall between the existing range 2000 - 5000. How do I check whether the input range falls within the existing range?

Answer 1

The easiest way to find an overlap is like this:

IF EXISTS (SELECT 1 FROM table WHERE @myValueLo <= ExistingRangeEnd AND @myValueHi >= ExistingRangeStart)
  -- Overlaps
ELSE
  -- Doesn't overlap

This can be shown to work if you compare the condition above against each of the bars in the diagram below:

Existing range:         |-------------------|
Overlaps:       |-------------|       |------------|
                |----------------------------------|
                           |-------------|
Not overlaps:   |-----|                       |----|

in all the overlap cases, both these tests are true:

• the start date of the existing range is always before the end date of the new range
• the end date of the existing range is after the start date of the new range

Those that don't overlap fail one or other of this test.