Geek Answers Handbook

Request for checking overlapping ranges in sql server?

I have a table like

From_Range ToRange 1 999 9000 10000 2000 5000

Now, when I try to insert the range values ​​as 1000 - 3000. It should fail, as some values ​​in this range are between the existing range 2000-5000. How to check if a range of input values ​​falls with an existing range or not?

+4
source share
2 answers

The easiest way to find the overlap:

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

It can be shown that this works if you compare the condition above with each of the columns in the diagram below:

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

in all cases of overlap, both of these tests are correct:

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

Those that do not intersect fail one or the other of this test.

+15
source

This works great 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 ) )
0
source

More articles:


All Articles