selecting where list elements are in a range

possible to do in a query or need to loop?

[LifeinAsia]

I have a MS SQL 2000 table with the following structure- 1st & second columns are integers (with 1st < 2nd), and 3rd column is a character string. Example:
1,5,'Range1'
10,11,'Range2'
13,35,'Range3'

I have a list of numbers that I need to check if they are in one of the ranges in the table, and if so grab the 3rd column.

For example, if the list was "1,6,11,16,21" I would want to return "'Range1','Range2'"
Actuallly, I would be even happier if it just verified that elements "1,11" returned matches.

Is there an easy way to do this within a single query? Or do I have to loop over the list and do a query for each element in the list? The table has about 15,000 records and the lists will run 4,000-8,000 elements. So I would much prefer to figure out some way to do it with just 1 database call instead of 1 call for each element!

[arran]

For example, if the list was "1,6,11,16,21" I would want to return "'Range1','Range2'"

Due to the occurrence of 16 and 21, wouldn't "Range3" also be returned.
If not, could you please explain the logic behind your example/schema.

Thanks,
arran.

[LifeinAsia]

Yes, sorry about that- it should have been "Range1,Range3"

[arran]

What about "Range2" given the occurrence of 11?

Sorry to nitpick, i'm just making sure I understand what you're trying to achieve :)

[LifeinAsia]

I'm trying to achieve a coherent sentence, and it's not working out today. :)

Let's try this again...

If the test list was "1,6,11,16,21" I'd like to get back that "1,11,16,21" were found to be in valid ranges. Or alternatively, that "6" was found not to be within any of the ranges.

[Demaestro]

Does the list you are wanting to check to see if the elements fall into range live in a table or get passed in as an incoming arg?

[LifeinAsia]

It could be setup either way. The list is coming from a subset of another query.

[Demaestro]

LIA I keep trying to write this out and I keep mstalling. So far I am thinking that a case statement is the right track. Although I am think I am stuck where you might be and that is doing it without looping through the values you want to check. I presume you want 1 record per value? Returning soething like:

value ¦ range
1 ¦ range1
2 ¦ range1
6 ¦ No Range Found

Type of thing.

I was thinking if you could loop through the logic to dynamically create SQL that you could run you could create a big union statement that would return the data all in one record set.

So something like:

sql = ''
for value_to_check in [1,2,6]:

sql = sql + """
Select
..case
....when
......value_to_check >= (select low_range1 from range_table) and value_to_check <= (select high_range1 from range_table)
....then
......value_to_check as value
......'Range1' as range
....when
......value_to_check >= low_range2 and value_to_check <= high_range2
....then
......value_to_check as value
......'Range2' as range
....when
......value_to_check >= low_range3 and value_to_check <= high_range3
....then
......value_to_check as value
......'Range3' as range
....else
......value_to_check as value
......'No Range Found' as range

**Unless end loop** 'Union All'
"""

Not sure if this is what you are looking for. If not I am willing to take another stab at it with you.

[LifeinAsia]

Not exactly...

We finally decided to just brute force it and loop through the list. We dumped the data from the table into a temp table (instead of hitting the database table each trip through the loop) and ran some tests. The performance wasn't as bad as we feared, so I guess we'll stick with the brute force method.

Thanks anyway!

[Demaestro]

No problem, sorry I didn't have more time yesterday to really get my head around it. If you ever go to optmize it I would be up for taking another stab at it.

[syber]

I think this will work for you:

SELECT num1, (SELECT range 
    FROM rangetable  
    WHERE numlist.num1 BETWEEN col1 AND col2) 
 FROM numlist 
 WHERE (SELECT range 
   FROM rangetable  
   WHERE numlist.num1 BETWEEN col1 AND col2) 
 IS NOT NULL