Always return something on a ZipCode Search

[mfitzgerald]

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

[abev]

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:

WHILE (SELECT REP FROM REPS WHERE REPZIP = @SEARCHEDZIP) 
 
BEGIN
 
    IF (SELECT COUNT(REP) FROM REPS WHERE REPZIP = @SEARCHEDZIP) > 0
       --we have the rep
        BREAK
 
    ELSE
   --increment the zip
    SET @SEARCHEDZIP = @SEARCHEDZIP + 1
        CONTINUE
 
END

It;s a long shot but maybe gets you closer?

[ck9663]

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:

select top 1 *  from YourZipCodeTable
where zipcode >= @YourZipCode
order by zipcode
 

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

[mfitzgerald1]

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:

select top 1 *  from YourZipCodeTable
where zipcode >= @YourZipCode
order by zipcode
 

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

[mfitzgerald1]

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:

       WHILE (SELECT REP FROM REPS WHERE REPZIP = @SEARCHEDZIP OR REPZIP = @SEARCHEDZIP2)
       BEGIN
           IF (SELECT COUNT(REP) FROM REPS WHERE REPZIP = @SEARCHEDZIP) > 0 || (SELECT COUNT(REP) FROM REPS WHERE REPZIP = @SEARCHEDZIP2) > 0
              --we have the rep
               BREAK
           ELSE
          --increment the zip
           SET @SEARCHEDZIP = @SEARCHEDZIP + 1
           SET @SEARCHEDZIP2 = @SEARCHEDZIP2 - 1
               CONTINUE
 
        CONTINUE
 
       END
 

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:

WHILE (SELECT REP FROM REPS WHERE REPZIP = @SEARCHEDZIP) 
 
BEGIN
 
    IF (SELECT COUNT(REP) FROM REPS WHERE REPZIP = @SEARCHEDZIP) > 0
       --we have the rep
        BREAK
 
    ELSE
   --increment the zip
    SET @SEARCHEDZIP = @SEARCHEDZIP + 1
        CONTINUE
 
END

It;s a long shot but maybe gets you closer?

[ck9663]

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.

declare @myzip char(5)
 
set @myzip = '00608'
 
 
select @myzip, zipcode, cast(zipcode as int)-cast(@myzip as int) as distance
from myZipCodeTable where cast(@myzip as smallint) between cast(zipcode as int) - 10 and cast(zipcode as int) + 10
order by abs(cast(zipcode as int)-cast(@myzip as int)), zipcode desc

Happy coding.

-- CK

[mfitzgerald1]

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.

declare @myzip char(5)
 
set @myzip = '00608'
 
 
select @myzip, zipcode, cast(zipcode as int)-cast(@myzip as int) as distance
from myZipCodeTable where cast(@myzip as smallint) between cast(zipcode as int) - 10 and cast(zipcode as int) + 10
order by abs(cast(zipcode as int)-cast(@myzip as int)), zipcode desc

Happy coding.

-- CK

[mfitzgerald1]

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