Welcome to WebmasterWorld Guest from 54.146.246.4

Forum Moderators: open

Message Too Old, No Replies

selecting where list elements are in a range

possible to do in a query or need to loop?

     

LifeinAsia

6:02 pm on Jun 5, 2006 (gmt 0)

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 5+ Year Member Top Contributors Of The Month



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

6:36 pm on Jun 5, 2006 (gmt 0)

10+ Year Member



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

6:41 pm on Jun 5, 2006 (gmt 0)

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 5+ Year Member Top Contributors Of The Month



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

arran

6:45 pm on Jun 5, 2006 (gmt 0)

10+ Year Member



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

8:22 pm on Jun 5, 2006 (gmt 0)

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 5+ Year Member Top Contributors Of The Month



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

8:37 pm on Jun 5, 2006 (gmt 0)

WebmasterWorld Senior Member demaestro is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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

8:46 pm on Jun 5, 2006 (gmt 0)

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 5+ Year Member Top Contributors Of The Month



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

Demaestro

10:18 pm on Jun 5, 2006 (gmt 0)

WebmasterWorld Senior Member demaestro is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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

11:14 pm on Jun 5, 2006 (gmt 0)

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 5+ Year Member Top Contributors Of The Month



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

3:09 pm on Jun 6, 2006 (gmt 0)

WebmasterWorld Senior Member demaestro is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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

1:31 pm on Jun 8, 2006 (gmt 0)

10+ Year Member



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