Query to check overlapping ranges in sql server?

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?

Answers


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.


This works fine for me

DECLARE @From Decimal = 2000
DECLARE @TO Decimal =5000

SELECT COUNT(ID)
FROM TABLENAME
WHERE  
(
 (
 @From BETWEEN OPEN_HRS AND (CLOSE_HRS - 1) 
 OR 
 @TO BETWEEN (OPEN_HRS + 1) AND CLOSE_HRS 
 ) 
OR
 (
 OPEN_HRS BETWEEN @From AND (@TO - 1)
 OR 
 CLOSE_HRS BETWEEN (@From + 1) AND @TO
 )
)

Need Your Help

Is return autorelease a bug in objective c?

objective-c memory-management autorelease

I am new to objective c and am trying to understand how/when autorelease is called. I understand the simple use case of:

"Target [App\Http\Controllers\Controller] is not instantiable."

php laravel controller routes laravel-5

I'm trying to follow laracasts tutorial on laravel fundamentals but after getting composer and laravel installed with no problems I can't get my routes file to work with the controller I've reinsta...