July 6, 2011 at 10:31 pm
I have a customer table with a zip code in it. I have a rep zip table with zip code ranges.
Customer:
customer_id I
Name C(25)
zip c(5)
Rep_Zip
Rep_id I
Zip_start C(5)
Zip_end C(5)
Data:
Customer
1 Mike 92646
2 Joe 92701
3 Jim 02711
Rep_zip
1 92000 93000
1 94000 94500
1 80000 90000
I want to know all customers that have a zip code in the range(s) for rep #1. Any ideas?
Thank you,
Mike
July 6, 2011 at 11:08 pm
For this to work, you certain assumption would have to be made. Are you only dealing with ZipCodes that are numeric in nature? All US zip codes are numeric. Get to Canada and they all start with a letter. Assuming that you are only dealing with US zip codes, then you could convert the zipcode to INT either permanently or inside the query (which is bad performance) and do something like follows:
Select *
from Customers as C
inner join Reps as R on C.ZipCode BETWEEN R.StartZip and R.EndZip
where R.Column = Blah
July 6, 2011 at 11:37 pm
Fraggle-805517 (7/6/2011)
For this to work, you certain assumption would have to be made. Are you only dealing with ZipCodes that are numeric in nature? All US zip codes are numeric. Get to Canada and they all start with a letter. Assuming that you are only dealing with US zip codes, then you could convert the zipcode to INT either permanently or inside the query (which is bad performance) and do something like follows:
Select *
from Customers as C
inner join Reps as R on C.ZipCode BETWEEN R.StartZip and R.EndZip
where R.Column = Blah
A Zip code might be all numeric or not. But its not a number and should not be stored like that. In the example above you have a zip starting with a 0 and if you convert that to an int you will lose that and have to add that in code later during presentation.
And there is no reason to convert to an int anyway. The BETWEEN works just as well on char as it does on int. And since all the zip columns are char there is no conversion needed.
So your code will work without any ifs or buts.
/T
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply