Need help, comparing tables but data is not consistent...

[belcom125]

I have a table from Sales team of their prospects of about 2000 records since 2003. fact is about 100 of those became customers. I need to compare the prospect table to the customer table in the database and as accurately as possible identify who became a customer. Now the problem is, that the data like Company for example is not consistent. The whole words might match but the whole customer name or if comparing by first 5-10 characters might not match. Phones, postal codes are not always exact too. And the addresses might have suite number that's not in the compared record.

I compare by first 3 and last 3 chars for the postal code, last 4 chars of a phone number (many prospects don't have phone at all), first 5 chars of customer name and first 4 chars of an address. I get about 50, however my manager tells me there should be a bit over a 100.

Does anyone know how I can extract whole words of a cutomer name and compare them to something matching in the prospects? OR any other suggestions would quite helpfull. Thanks. I am on a deadline, please help me out.

 

[SQLSister]

First thing you need to do is fix your data entry problem. No customer should be able to be entered that exists inthe prospecitve database without creating a direct link to it. Either by requiring the use of the same name or storing the id field for the propsective customer inthe table for the customers.

There is no way to accurately identify the linkages right now except by hand because the changes can be so very nummerous and may happen inthe first charater as easily as the last. Even using the methods you described will still need hand evaluation because there are in fact different companies with similar names.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[belcom125]

Thanks for your reply. I don't have any control over the data entry problem. It was submitted to us by a 3rd party telemarketing company who entered all of it through a web interface which obviously didn't have anything to verify the data by. And it was submitted in Excel format. I imported the data into a separate table. And now I am faced with the problem. I know that it is only maybe 50-60% accurate. However my manager didn't tell me to leave this alone yet )
I am trying to explain, but he's busy.

 

[SQLSister]

Well you can look at Soundex in BOL for some help. YOu will still have to examine the results and decide for yourself which ones are real matches. For instance Siemens and Siemon are close matches in soundex, but two different companies.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief