Need Grouping Solution

[cguyreports]

I am in need of assistance to create an efficient way of grouping states.

I have a table with over 2 million records and need to show a count by state. However the state field is not always consistent. The data type is varchar 15 and I have data ranging for the standard two char abbreviations to the state being spelled out or just plain junk with no meaning. I have thought about creating a case statement or a lookup table but was hoping of a more efficient manner to handle the data problems.

Any words of insight would be very helpful. Thanks in advance for your time!

 

[SQLSister]

The best bet is to clean up the data first and then write code to not allow junk to be inserted into the field ever again.

Write an update to update all the long state names to the two letter abbreviation and then put a trigger onthe table which will not allow any value to be inserted that do not match the two letter codes and converting any exact matches onthe long name to the two letter codes. Trust me, you will be glad you did this. Then you can count by state becasue you will have all the states you can identify inthe two letter abbreviation. And anything which doesn;t match that you can call up easily for later clean up.

At least onece you have done the clean up once and written a trigger to prevent it from getting bad again, you will alwys be able to just use the state abbreviations in your queries with no costly finagling around to get counts, etc.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[cguyreports]

Thank you very much for your reply. In this case I have not control over the data in this table. I am just dealing with junk.

 

[JPMontreal]

Hi,
If the zip code is consistent, you could use it to clean up your table before the grouping.
I had a similar problem once and I downloaded from the US Post web site the list of States with the zip codes ranges for every state.
You could use a query that update the state if the code is in a range:

WHEN LEFT(CPOST,2)>= '07' THEN 'NJ'
WHEN LEFT(CPOST,2)>= '06' THEN 'CT'
WHEN LEFT(CPOST,2)>= '05' THEN 'VT'
WHEN LEFT(CPOST,3)>= '028' THEN 'RI'
WHEN LEFT(CPOST,2)>= '01' THEN 'MA'

Regards.


Jean-Paul
Montreal
To send me E-Mail, remove “USELESSCODE”.
jp@USELESSCODEsolutionsvba.com

 

[SQLSister]

Ok so what we have here is a political problem not a SQL problem. I am assumming the person who does control the data doesn;t want to be bothered to help fix the problem?

If you have no control over the data, you need to get to the boss of whoever does have control over the data. Create your report with a lookup table or case statement. Document how long it takes to run and why that is so. Then get your boss to talk to the boss of the person who controls the data about why this is taking so long and how it is putting a hurt on the system to processs and that by investing in a one-time clean up it will shorten the processing time considerably (it's best if you can get a test set of cleaned up data to run against to show the difference) which is important when you have databases of this size. And you will have better data integrity as well by forcing the user to put in correct states. You need to sell this idea to management.

If you can't get them to fix the problem on thier end, then there is just no efficiant way to process this data and you are stuck with the case or lookup approach. Try both and see which is faster. But at least if you document why this is a problem to your boss, you won't get the blame for the way this ties up your system processing becasue you clearly tried to get it fixed.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief