473,320 Members | 2,035 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

sql to find a numeric range inside another range?

hi,

i'm building a query to find narrow ranges of zip codes within broader
ranges in a table (for tax purposes). for example:

LOW_ZIP HI_ZIP
23400 23499

need to find
23401 23402
23403 23440
23441 23... you get the picture. :)

so, each row has a low value and a hi value, then i need to run a
subquery to take those values and find narrower ranges. any ideas?
Jul 19 '05 #1
3 10551

"dbarchitech" <no*******@mac.com> wrote in message
news:9d**************************@posting.google.c om...
| hi,
|
| i'm building a query to find narrow ranges of zip codes within broader
| ranges in a table (for tax purposes). for example:
|
| LOW_ZIP HI_ZIP
| 23400 23499
|
| need to find
| 23401 23402
| 23403 23440
| 23441 23... you get the picture. :)
|
| so, each row has a low value and a hi value, then i need to run a
| subquery to take those values and find narrower ranges. any ideas?

yeah, but you got to show us yours first ;-)

;-{ mcs
Jul 19 '05 #2
"Mark C. Stock" <mcstockX@Xenquery .com> wrote in message news:<eP********************@comcast.com>...
"dbarchitech" <no*******@mac.com> wrote in message
news:9d**************************@posting.google.c om...
| hi,
|
| i'm building a query to find narrow ranges of zip codes within broader
| ranges in a table (for tax purposes). for example:
|
| LOW_ZIP HI_ZIP
| 23400 23499
|
| need to find
| 23401 23402
| 23403 23440
| 23441 23... you get the picture. :)
|
| so, each row has a low value and a hi value, then i need to run a
| subquery to take those values and find narrower ranges. any ideas?

yeah, but you got to show us yours first ;-)

;-{ mcs


yeah, well mine doesn't work!!! :-$

select sales_tax_id,
from_postal_code,
to_postal_code
from ar_sales_tax a,
(select from_postal_code as fpc,
to_postal_code as tpc
from ar_sales_tax
where rowid = rowid) b
where from_postal_code between b.fpc and b.tpc
and to_postal_code between b.fpc and b.tpc
and end_date is null

i need to somehow scan the entire table row by row looking for records
that fit within the ranges... i'm thinking i may need to do this using
a cursor.

-cam
Jul 19 '05 #3

"dbarchitech" <no*******@mac.com> wrote in message
news:9d**************************@posting.google.c om...
| "Mark C. Stock" <mcstockX@Xenquery .com> wrote in message
news:<eP********************@comcast.com>...
| > "dbarchitech" <no*******@mac.com> wrote in message
| > news:9d**************************@posting.google.c om...
| > | hi,
| > |
| > | i'm building a query to find narrow ranges of zip codes within broader
| > | ranges in a table (for tax purposes). for example:
| > |
| > | LOW_ZIP HI_ZIP
| > | 23400 23499
| > |
| > | need to find
| > | 23401 23402
| > | 23403 23440
| > | 23441 23... you get the picture. :)
| > |
| > | so, each row has a low value and a hi value, then i need to run a
| > | subquery to take those values and find narrower ranges. any ideas?
| >
| > yeah, but you got to show us yours first ;-)
| >
| > ;-{ mcs
|
| yeah, well mine doesn't work!!! :-$
|
| select sales_tax_id,
| from_postal_code,
| to_postal_code
| from ar_sales_tax a,
| (select from_postal_code as fpc,
| to_postal_code as tpc
| from ar_sales_tax
| where rowid = rowid) b
| where from_postal_code between b.fpc and b.tpc
| and to_postal_code between b.fpc and b.tpc
| and end_date is null
|
| i need to somehow scan the entire table row by row looking for records
| that fit within the ranges... i'm thinking i may need to do this using
| a cursor.
|
| -cam

well, know we know that you tried something!

you won't need a cursor, and you won't need to be one either ;-)

i'll take a stab at it, but it would be nice if you could elaborate on
'doesn't work'

regarding your query

select ...
from ar_sales_tax a
,(
select ...
from ar_sales_tax
where rowid = rowid
) b

the rowid=rowid is always true, don't you think?
so there is no added value in that statement
and there is no added value in the in-line view (the in-line subquery you've
aliased as 'b'

so, if that's simplified to
select ...
from ar_sales_tax a
, ar_sales_tax b
where a.from_postal_code between b.from_postal_code and
b.to_postal_code
and a.to_postal_code between b.from_postal_oce and
b.to_postal_code
and a.end_date is null

all that's going to do for you is give you rows in the 'A' pass at your
table where rows are found in the 'B' pass with a range of codes that
overlap each of the codes from the 'A' pass -- but it doesn't tell you
anything about the relationship between the two passes, and doesn't limit
the results to any specific pair of codes

assuming you're looking for ranges narrower than your 23400/23499 sample,
how about narrowing one of the passes by that pair of values? or returning
the range from the A pass and the range from the B pass in your select
clause, so that you've got something to work with?

;-{ mcs
Jul 19 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Stephen Boulet | last post by:
I have a long list of floats (1613808 elements long). It takes quite a while to load this into a 7 dimensional Numeric array. Is there any obvious way to speed this up? def...
0
by: beliavsky | last post by:
I have been looking at the speed of Python with the Numeric module by simulating some random numbers and computing some statistics. Here is the code. Line (1) is replaced as shown in the table. ...
24
by: Matt Feinstein | last post by:
Hi all-- I'm new to Python, and was somewhat taken aback to discover that the core language lacks some basic numerical types (e.g., single-precision float, short integers). I realize that there...
1
by: Luigi Ida' via SQLMonster.com | last post by:
Hi, I have a php application connected through odbc to a sqlserver database. When I try to execute select queries on a smalldatetime table field I receive this message: Warning: Numeric value...
30
by: Dr John Stockton | last post by:
It has appeared that ancient sources give a method for Numeric Date Validation that involves numerous tests to determine month length; versions are often posted by incomers here. That sort of code...
2
by: MrNobody | last post by:
I just noticed that a numeric up/down will let you manually type in any number even if it is beyond the min/max range you specified. The control will not actually return this value if it is...
7
by: Sheldon | last post by:
Hi, I have the following loop that I think can be written to run faster in Numeric. I am currently using Numeric. range_va = main.xsize= 600 main.ysize= 600 #msgva is an (600x600) Numeric...
2
by: tron_23 | last post by:
hi, we use Toplink (TopLink - 4.6.0 (Build 417) with a DB2 Database 7.2. i know really old versions, but we could change to e newer one ;-) Sometimes we got some problems with update or insert...
3
by: dbarchitech | last post by:
hi, i'm building a query to find narrow ranges of zip codes within broader ranges in a table (for tax purposes). for example: LOW_ZIP HI_ZIP 23400 23499 need to find 23401 ...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.