Comparing a list of addresses to a list of ranges

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

Transact-SQL (2000)

Comparing a list of addresses to a list of ranges

Author

Topic

jmiller121
Starting Member

8 Posts

Posted - 2008-08-19 : 12:36:47

My Goal: To take a table of Addresses and compare them to a table of Address Ranges.

Addresses Table Field Layout (tbl1):
HouseNumber
Street
City

Address Range Table Field Layout (tbl2):
HouseNumberFrom
HouseNumberTo
Street
City

What I have is about 100,000 actual addresses, and what I need to do is compare each one of them to the Address Range table to see it falls within an entry on the range table or not. I need a way to mark each address in tbl1 either True or False based on whether the following statement is True or False...:
"tbl1.HouseNumber" >= "tbl2.HouseNumberFrom" AND
"tbl1.HouseNumber" <= "tbl2.HouseNumberTo" AND
"tbl1.Street" = "tbl2.Street" AND
"tbl1.City" = "tbl2.City"

Example:

Address Range
HouseNumberFrom: 1000
HouseNumberTo: 2000
Street: Main St
City: Columbus

(Assuming there are no other entries for Main St...)
1234 Main St, Columbus should return TRUE
2345 Main St, Columbus should return FALSE

Just not sure how (or if it's possible) to write and SQL statement to do that.....

Thanks For Your Help!
Jason Miller
jmiller121@gmail.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-19 : 12:49:21

[code]SELECT a.*,
CASE WHEN a.HouseNumber BETWEEN ar.HouseNumberFrom AND ar.HouseNumberTo THEN 'True' ELSE 'False' END
FROM Addresses a
INNER JOIN AddressRange ar
ON ar.Street=a.Street
AND ar.City=a.City[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources