Checking a number from a range, contained within a range under SQL

Working on an application where transactions are created that log a range of numbers, i.e.

SysTranId, RangeFrom,RangeTo

So user enters a range in the system and I want to check whether any numbers in the users range, are contained within any of the ranges stored in the transactions.

i.e. Users Range 100010, 100022 (numbers 100010 through to 100022)

Have any of these numbers been used, regardless of the range (so 100015 could have been used in range 100000 to 1000015 and 100022 could have been used in range 100020 to 100025)

Is this something that can be done easily in SQL. I can do this by cycling through the table, which while there are a few hundred records won’t be too bad, but this application will have hundreds of thousands of records.

Any ideas gratefully received

Colin

Most database systems (since you didn’t mention which one I can’t be more specific) have a BETWEEN statement, normally used like this:

SELECT *
FROM table
WHERE col BETWEEN 5 AND 100

Substitute ‘5’ and ‘100’ for the user-inputted values and you’re laughing.

Cheers,
D.

disgracian, the problem here is that there isn’t a single “col” that the BETWEEN can operate on, there two of them – RangeFrom and RangeTo

let’s set up a timeline to illustrate the problem

let’s say that the user enters numbers in a START and END range, and you have transactions defined using RangeFrom and RangeTo numbers

here are all the ways that a transaction overlap the user’s numbers –


                     START                  END
                       |                     |
1 RangeFrom---RangeTo  |                     |
                       |                     |
2           RangeFrom--|--RangeTo            |
                       |                     |
3                      | RangeFrom---RangeTo |
                       |                     |
4          RangeFrom---|---------------------|---RangeTo
                       |                     |
5                      |          RangeFrom--|--RangeTo
                       |                     |
6                      |                     |  RangeFrom---RangeTo

with me so far?

now, all we have to do is choose every transaction that satisfies one of the conditions 2 through 5

the easiest way is like this –


... WHERE RangeTo >= START  /* eliminates case 1 */
      AND RangeFrom <= END  /* eliminates case 6 */

notice that it’s an AND which means both of those have to be true

Thanks Rudy. I could have sworn I tried that a while back, but it didn’t work. Oh well, all works now. Thanks again. Colin