mostym
asked on
A new problem with zipcodes..Challenging
I have some zipcodes that are in 5 digits..
the base as in USpS is 5 digit and these zipcodes are assigned to codes
Eg
Zip Code
005019999 A101
005449999 A102
....
.
.
.
.
and so on so the default +4 is 9999 for the zipcodes...
Now the assignments is done on 5 digit bases but if their is a 4 digit assigned to someone else from the base 5 that appears as data so example
zip code
381370000 Q328
381383904 Q104
381383929 Q104
381383930 Q104
381389999 Q104
381390000 Q328
So the base owner is Q328 but than some of the zips wchich have +4's belong to anoterh code..
Now i need to find the ranges in zip codes
So it should do
zmin zmax code
38170000 381389203 Q238
381389204 381389204 Q104
381389205 381383928 Q328
381043929 381383930 Q104
....
so the number between i.e the number that is not an exception should get another range and then get assigned to the base owner....
Does someone has any suggestions...
Mostym
the base as in USpS is 5 digit and these zipcodes are assigned to codes
Eg
Zip Code
005019999 A101
005449999 A102
....
.
.
.
.
and so on so the default +4 is 9999 for the zipcodes...
Now the assignments is done on 5 digit bases but if their is a 4 digit assigned to someone else from the base 5 that appears as data so example
zip code
381370000 Q328
381383904 Q104
381383929 Q104
381383930 Q104
381389999 Q104
381390000 Q328
So the base owner is Q328 but than some of the zips wchich have +4's belong to anoterh code..
Now i need to find the ranges in zip codes
So it should do
zmin zmax code
38170000 381389203 Q238
381389204 381389204 Q104
381389205 381383928 Q328
381043929 381383930 Q104
....
so the number between i.e the number that is not an exception should get another range and then get assigned to the base owner....
Does someone has any suggestions...
Mostym
ASKER
actualy the data ove needs to be changed..
zip code
381370000 Q328
381383904 Q104
381383929 Q104
381383930 Q104
381389999 Q328
381390000 Q328
The range shud be
zmin zmax code
38170000 381383903 Q238
381383904 381383904 Q104
381383905 381383928 Q328
381043929 381383930 Q104
381043931 381389999 Q328
the table has again more then 100000 records......and performance is of course an issue....
so if the base exists than we shud have a range for the base and the base then needs to be split in to plus four
i.e if i have just this
381051235 Q111
381059999 Q123
381069999 Q123
theh the range for this will be
zmin zmax code
381050000 381051234 Q123
381051235 381051235 Q111
381051236 381069999 Q133
the numbers will always be arranged in order ....
thanks Guys...
zip code
381370000 Q328
381383904 Q104
381383929 Q104
381383930 Q104
381389999 Q328
381390000 Q328
The range shud be
zmin zmax code
38170000 381383903 Q238
381383904 381383904 Q104
381383905 381383928 Q328
381043929 381383930 Q104
381043931 381389999 Q328
the table has again more then 100000 records......and performance is of course an issue....
so if the base exists than we shud have a range for the base and the base then needs to be split in to plus four
i.e if i have just this
381051235 Q111
381059999 Q123
381069999 Q123
theh the range for this will be
zmin zmax code
381050000 381051234 Q123
381051235 381051235 Q111
381051236 381069999 Q133
the numbers will always be arranged in order ....
thanks Guys...
ASKER
Look at these posts these will help understanding that the problem is not that simple......
there are around 500 codes and 1000000 zipcodes..
Well the problem is that the range will split....the min and max will then result in overlaps.....
https://www.experts-exchange.com/questions/21076168/Sql-Server-Query.html
there are around 500 codes and 1000000 zipcodes..
Well the problem is that the range will split....the min and max will then result in overlaps.....
https://www.experts-exchange.com/questions/21076168/Sql-Server-Query.html
This can be done easily using DTS. I noticed people in your previous questions used queries and temp tables but in my opinion this is too complicated, although it might provide somewhat better performance. If you're interested in a solution using DTS let me know and I will elaborate.
ASKER
Sure DTS will help too...any solutions will definitely help me resolve the problem, Please let me what do u have in yur mind.
Thanks.
Thanks.
Yes, we're all ears.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I ran it for sometime but it was taking too much time to run..again as i have 100000 records it takes too much time...i will run it in steps and ssee if it gives right results
What kind of hardware are you running this on?
ASKER
SQL Server 200o and i think i have a P4
What kind of hard drives? You might want to consider a low-level Xeon/Opteron system with 2 or 3 drives (preferably SCSI) in a RAID 0 or 5 configuration.
Also, how much memory? What is the size of the table (in KB)?
Furthermore, consider creating indexes on VOwner and VNotOwner (on ZIP); that could speed things up quite nicely.
Furthermore, consider creating indexes on VOwner and VNotOwner (on ZIP); that could speed things up quite nicely.
Just dropping in to say I see you are still at it Rick. I'd like to help and Sjoerd is one of the most capable experts here, but I think your reoccuring problem is that you are not explaining the problem in a way we can understand.
I wonder if you could explain where the data comes from, what you want to do with it, and why? That's usually the opposite of what we usually ask - just give us the data input and desired output is the norm - but we might be trying to develop a solution around your preconceptions of how it needs to be done when there is another more efficient solution.
I wonder if you could explain where the data comes from, what you want to do with it, and why? That's usually the opposite of what we usually ask - just give us the data input and desired output is the norm - but we might be trying to develop a solution around your preconceptions of how it needs to be done when there is another more efficient solution.
ASKER
Sorry got back so late in here guys Well let me explain the problem amd may be u guys can work with the logic i was trying to use the logic i thought would work best to get the ranges...
I have a database which has 50000 zipcodes, these zipcodes are assigned to codes as i had suggested..
The way the data is in the table is that
i have a cloumn which has only 5 digits and another column which has 4 digit zips....
the primary key is a combination of these two columns
eg
91234 9999
The way codes are assigned to the zipcodes is that if the base owner i.e the owner of 91234 9999 will get all the zip codes in the zip 91234..i.e 91234 0000 to 91234 9999 except for if someone else is specifically assigend a code in that +4 i.e
91234 9999 A101 means everything in 91234 0000 to 91234 9999 should go to A101 but we do not have the +4's in the database becasue there are no exceptions to this so i will just have 91234 9999
but i can have exceptions like i have a case where
91233 9999 A100
91234 9999 A123
91235 9999 A101
91235 1234 A123
91235 2000 A123
91236 9999 A101
if we look above the range should be
91233 0000 91233 9999 A100
91234 0000 91234 9999 A123
91235 0000 91235 1233 A101
91235 1234 91235 1234 A123
91235 1235 91235 1999 A101
91235 2000 91235 2000 A123
91235 2001 91236 9999 A101
thats just one case
lets look athe the other
91233 9999 A123
91234 9999 A123
91235 9999 A101
91235 1234 A123
91235 2000 A123
91236 9999 A123
Now the ranges would change
91233 0000 91234 9999 A123
91235 0000 91235 1233 A101 < because the base owner i.e owner of 9999 is A101
91235 1234 91235 1234 A123
91235 1235 91235 1999 A101
91235 2000 91235 2000 A123
91235 2001 91235 9999 A101
91236 0000 91236 9999 A123
now lets look at another example
91789 9999 A101
91790 9999 A101
91799 9999 A101 <-----the gap here should be identifed as a gap..and then assigned to the right code which will be A101
91801 9999 A101
91803 9999 A101
91804 9999 A101
91805 9999 A102
91806 9999 A102
91807 9999 A102
91808 9999 A102
now here my ranges are
91789 0000 91790 9999 A101
91799 0000 91799 9999 A101
91801 0000 91801 9999 A101
91803 0000 91804 9999 A101
91805 0000 91808 9999 A102
the zip codes that are no in here should be assigned using the gaps logic becasue they are actualy gaps......if we can get the ranges i can get the gaps from there but the tough part is to get the ranges and handle all these exceptions....
now just two zipcodes belong to another code rest all are A101..
I need to find the ranges for all the zipcodes as i have suggested...
in the above cases i will have the range broken down...
I dont have 0000 in +4 ....... only if there is an exception it will have a +4 and thats is why i am not able to find the ranges....
Does this help Kselvia ..
I can go in to more detail if you want me to ..please let me know..thanks s ton for your help guys
I have a database which has 50000 zipcodes, these zipcodes are assigned to codes as i had suggested..
The way the data is in the table is that
i have a cloumn which has only 5 digits and another column which has 4 digit zips....
the primary key is a combination of these two columns
eg
91234 9999
The way codes are assigned to the zipcodes is that if the base owner i.e the owner of 91234 9999 will get all the zip codes in the zip 91234..i.e 91234 0000 to 91234 9999 except for if someone else is specifically assigend a code in that +4 i.e
91234 9999 A101 means everything in 91234 0000 to 91234 9999 should go to A101 but we do not have the +4's in the database becasue there are no exceptions to this so i will just have 91234 9999
but i can have exceptions like i have a case where
91233 9999 A100
91234 9999 A123
91235 9999 A101
91235 1234 A123
91235 2000 A123
91236 9999 A101
if we look above the range should be
91233 0000 91233 9999 A100
91234 0000 91234 9999 A123
91235 0000 91235 1233 A101
91235 1234 91235 1234 A123
91235 1235 91235 1999 A101
91235 2000 91235 2000 A123
91235 2001 91236 9999 A101
thats just one case
lets look athe the other
91233 9999 A123
91234 9999 A123
91235 9999 A101
91235 1234 A123
91235 2000 A123
91236 9999 A123
Now the ranges would change
91233 0000 91234 9999 A123
91235 0000 91235 1233 A101 < because the base owner i.e owner of 9999 is A101
91235 1234 91235 1234 A123
91235 1235 91235 1999 A101
91235 2000 91235 2000 A123
91235 2001 91235 9999 A101
91236 0000 91236 9999 A123
now lets look at another example
91789 9999 A101
91790 9999 A101
91799 9999 A101 <-----the gap here should be identifed as a gap..and then assigned to the right code which will be A101
91801 9999 A101
91803 9999 A101
91804 9999 A101
91805 9999 A102
91806 9999 A102
91807 9999 A102
91808 9999 A102
now here my ranges are
91789 0000 91790 9999 A101
91799 0000 91799 9999 A101
91801 0000 91801 9999 A101
91803 0000 91804 9999 A101
91805 0000 91808 9999 A102
the zip codes that are no in here should be assigned using the gaps logic becasue they are actualy gaps......if we can get the ranges i can get the gaps from there but the tough part is to get the ranges and handle all these exceptions....
now just two zipcodes belong to another code rest all are A101..
I need to find the ranges for all the zipcodes as i have suggested...
in the above cases i will have the range broken down...
I dont have 0000 in +4 ....... only if there is an exception it will have a +4 and thats is why i am not able to find the ranges....
Does this help Kselvia ..
I can go in to more detail if you want me to ..please let me know..thanks s ton for your help guys
Have you tried putting indexes on vowner and vnotowner and running the query as I gave it?
ASKER
Yes i have but it is still slow and takes a long time to run.
In that case, you might just have to use a beefier machine (first, try more memory).
SELECT zmin, zmax, a.code FROM
(SELECT Code, max(zip) as zmax FROM TableA GROUP BY Code ) as a INNER JOIN
(SELECT Code, min(zip) as zmin FROM TableA GROUP BY Code) b ON a.code = b.code