By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,466 Members | 1,148 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,466 IT Pros & Developers. It's quick & easy.

Always return something on a ZipCode Search

P: 1
Hi, I've got a table of Sale Reps (ID, Name, Address, City, State, Zipcode, etct). I'm trying to create a stored proc that will accept a ZipCode and search for Reps with that ZipCode. If there isn't an exact match I want the query to expand in increments of 1, up and down, until it finds a rep in the closest ZipCode.

I don't do much SQL, but I have a feeling this is a straightforward query, I'm just not seeing it.

Thanks so much.

-Michael
http://www.radiantdays.com
Feb 12 '08 #1
Share this Question
Share on Google+
7 Replies


P: 22
Hi, I've got a table of Sale Reps (ID, Name, Address, City, State, Zipcode, etct). I'm trying to create a stored proc that will accept a ZipCode and search for Reps with that ZipCode. If there isn't an exact match I want the query to expand in increments of 1, up and down, until it finds a rep in the closest ZipCode.

I don't do much SQL, but I have a feeling this is a straightforward query, I'm just not seeing it.

Thanks so much.

-Michael
http://www.radiantdays.com

michael I dont have a direct answer but lets brainstorm...

psedo tsql code:

Expand|Select|Wrap|Line Numbers
  1. WHILE (SELECT REP FROM REPS WHERE REPZIP = @SEARCHEDZIP) 
  2.  
  3. BEGIN
  4.  
  5.     IF (SELECT COUNT(REP) FROM REPS WHERE REPZIP = @SEARCHEDZIP) > 0
  6.        --we have the rep
  7.         BREAK
  8.  
  9.     ELSE
  10.    --increment the zip
  11.     SET @SEARCHEDZIP = @SEARCHEDZIP + 1
  12.         CONTINUE
  13.  
  14. END
It;s a long shot but maybe gets you closer?
Feb 13 '08 #2

ck9663
Expert 2.5K+
P: 2,878
Hi, I've got a table of Sale Reps (ID, Name, Address, City, State, Zipcode, etct). I'm trying to create a stored proc that will accept a ZipCode and search for Reps with that ZipCode. If there isn't an exact match I want the query to expand in increments of 1, up and down, until it finds a rep in the closest ZipCode.

I don't do much SQL, but I have a feeling this is a straightforward query, I'm just not seeing it.

Thanks so much.

-Michael
http://www.radiantdays.com

Let's see if I understand you right. You have a zip code(ie. zip = '00607'). You'll search the zip code table. If it's existing, it will return the row, and of course all the columns in it. If it's not, you'll search '00608'. Since the next valid zip code is '00610', you will have to increment the zip code value, until it reach '00610', return that record and get the other columns that you need.

If I am wrong, please don't continue reading. If am right, you can create a simple stored proc with this query inside:

Expand|Select|Wrap|Line Numbers
  1. select top 1 *  from YourZipCodeTable
  2. where zipcode >= @YourZipCode
  3. order by zipcode
  4.  
Your objective is to get the first record that exactly match the zip code you're looking for or @yourzipcode+1..N = ZipCodeOnTheTable. Which means if not existing, it will always return the first ZipCodeOnTheTable that's greater than @yourzipcode.

I hope I make sense.

Good luck.

-- CK
Feb 13 '08 #3

P: 4
CK,
Thanks so much. Yes, you're understanding me and this makes sense.I think your answer gets me halfway there. But they want the search to expand both up and down. Meaning if we start with 00608 but that returns nothing, they want to go to 00609, then DOWN to 00607, then try 00610, then 00606, so it's an ever-expanding set away from the original.

Our VPN is down, but I'll fiddle with this when I get in in the morning.

Thanks again.
-m



Let's see if I understand you right. You have a zip code(ie. zip = '00607'). You'll search the zip code table. If it's existing, it will return the row, and of course all the columns in it. If it's not, you'll search '00608'. Since the next valid zip code is '00610', you will have to increment the zip code value, until it reach '00610', return that record and get the other columns that you need.

If I am wrong, please don't continue reading. If am right, you can create a simple stored proc with this query inside:

Expand|Select|Wrap|Line Numbers
  1. select top 1 *  from YourZipCodeTable
  2. where zipcode >= @YourZipCode
  3. order by zipcode
  4.  
Your objective is to get the first record that exactly match the zip code you're looking for or @yourzipcode+1..N = ZipCodeOnTheTable. Which means if not existing, it will always return the first ZipCodeOnTheTable that's greater than @yourzipcode.

I hope I make sense.

Good luck.

-- CK
Feb 13 '08 #4

P: 4
Abev,
Thanks so much. I'll try it. It looks like it might work, but since they want the search to go both up and down, maybe I'll need to set a second searchzip. Something like this:
Expand|Select|Wrap|Line Numbers
  1.        WHILE (SELECT REP FROM REPS WHERE REPZIP = @SEARCHEDZIP OR REPZIP = @SEARCHEDZIP2)
  2.        BEGIN
  3.            IF (SELECT COUNT(REP) FROM REPS WHERE REPZIP = @SEARCHEDZIP) > 0 || (SELECT COUNT(REP) FROM REPS WHERE REPZIP = @SEARCHEDZIP2) > 0
  4.               --we have the rep
  5.                BREAK
  6.            ELSE
  7.           --increment the zip
  8.            SET @SEARCHEDZIP = @SEARCHEDZIP + 1
  9.            SET @SEARCHEDZIP2 = @SEARCHEDZIP2 - 1
  10.                CONTINUE
  11.  
  12.         CONTINUE
  13.  
  14.        END
  15.  
I'll try it when I get in in the morning (VPN went down) and let you know. Thanks again.
-m

michael I dont have a direct answer but lets brainstorm...

psedo tsql code:

Expand|Select|Wrap|Line Numbers
  1. WHILE (SELECT REP FROM REPS WHERE REPZIP = @SEARCHEDZIP) 
  2.  
  3. BEGIN
  4.  
  5.     IF (SELECT COUNT(REP) FROM REPS WHERE REPZIP = @SEARCHEDZIP) > 0
  6.        --we have the rep
  7.         BREAK
  8.  
  9.     ELSE
  10.    --increment the zip
  11.     SET @SEARCHEDZIP = @SEARCHEDZIP + 1
  12.         CONTINUE
  13.  
  14. END
It;s a long shot but maybe gets you closer?
Feb 13 '08 #5

ck9663
Expert 2.5K+
P: 2,878
Try running this on your query analyzer and play around with the value of @myzip variable if it keeps returning the right one. You have to include TOP 1 when you're satisfied with the result. I just included the zipcode and distance column so that you can further analyze the result.

One last catch: It will only look at the zipcode +/- 10. If you need to extend the range, you just have to increase it.

Expand|Select|Wrap|Line Numbers
  1. declare @myzip char(5)
  2.  
  3. set @myzip = '00608'
  4.  
  5.  
  6. select @myzip, zipcode, cast(zipcode as int)-cast(@myzip as int) as distance
  7. from myZipCodeTable where cast(@myzip as smallint) between cast(zipcode as int) - 10 and cast(zipcode as int) + 10
  8. order by abs(cast(zipcode as int)-cast(@myzip as int)), zipcode desc
Happy coding.

-- CK
Feb 13 '08 #6

P: 4
CK,
That's very cool. Thanks.
-m

Try running this on your query analyzer and play around with the value of @myzip variable if it keeps returning the right one. You have to include TOP 1 when you're satisfied with the result. I just included the zipcode and distance column so that you can further analyze the result.

One last catch: It will only look at the zipcode +/- 10. If you need to extend the range, you just have to increase it.

Expand|Select|Wrap|Line Numbers
  1. declare @myzip char(5)
  2.  
  3. set @myzip = '00608'
  4.  
  5.  
  6. select @myzip, zipcode, cast(zipcode as int)-cast(@myzip as int) as distance
  7. from myZipCodeTable where cast(@myzip as smallint) between cast(zipcode as int) - 10 and cast(zipcode as int) + 10
  8. order by abs(cast(zipcode as int)-cast(@myzip as int)), zipcode desc
Happy coding.

-- CK
Feb 13 '08 #7

P: 4
CK,
Thanks again. This worked perfect.

And thank Abev. I futz with the While Loop for... well...a while and couldn't get it. But I'm sure it would have eventually.
I appreciate both your time.

-michael
Feb 13 '08 #8

Post your reply

Sign in to post your reply or Sign up for a free account.