Returning numeric ranges as single list

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Returning numeric ranges as single list

Author

Topic

jaroot
Starting Member

46 Posts

Posted - 2006-01-24 : 16:18:37

I need to get a list of ranges for comparison as a single list.

So from a table with the following data:


STARTRANGE  ENDRANGE
350           352
355           355
359           360

I'd like to end up with a resultset containing


RANGELIST
350,351,352,355,359,360

I know I can probably do it using a cursor, but I'd kind of like to avoid using one if possible. I also need to use that result set as a
value for comparison in another table.

It's a list of zip codes in another table.. and I need to determine if the zip code falls in the specified ranges.

So 35099 would fall in the range
But 35399 would not.

Any help would be greatly appreciated!

Thanks!

SamC
White Water Yakist

3467 Posts

Posted - 2006-01-24 : 17:36:01

If you want to check to see if a zip code falls in any of the ranges in [STARTRANGE...ENDRANGE], I'd do it differently than building a list. (You will need a Tally table to build the list.)

Here's a [untried] query that returns 1 if the zipcode is in range and 0 if not...


SELECT CASE WHEN COUNT(*) = 0 THEN 0 ELSE 1 END AS ZipcodeInRange
  FROM dbo.RangeTable
  WHERE @ZipCode/100 BETWEEN STARTRANGE AND ENDRANGE -- Reduce zipcode to 3 digits and check

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources