Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 583
  • Last Modified:

SQL - determine if a given number does not lie within any range of numbers within a set of ranges

Is there an easy way to determine if a given integer is not within a set of ranges of integers?  I have a table - tblRange with columns intStart and intEnd.  Each record in tblRange consists of records similar to this: intStart = 5, intEnd = 10; intStart = 1, intEnd = 6; intStart = 11, intEnd = 20, etc.  For each record, intStart is always >= intEnd and intStart >= 0.  Given n, how can determine if n does not lie within (inclusive) any of tblRange's records ranges?
0
crayola3
Asked:
crayola3
  • 2
2 Solutions
 
MoreHeroicCommented:
SELECT *
FROM tblRange
WHERE n > intStart AND n < intEnd

If records are returned n is in that record's range.  if no records are returned it lies outside all the ranges.
0
 
MoreHeroicCommented:
Ah, I see inclusive, sorry:

SELECT *
FROM tblRange
WHERE n >= intStart AND n <= intEnd
0
 
sdstuberCommented:
   "intStart is always >= intEnd and intStart >= 0"

is that an error?

from your examples  intStart < intEnd.  not >=

can intStart be greater than intEnd?  can they be equal?
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now