By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
448,753 Members | 2,071 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 448,753 IT Pros & Developers. It's quick & easy.

Validating Multiple Records in Same Table

P: 14
Good evening all, I have a problem that I've been banging my head against for a few hours and was looking for some advice. Consider a table with the following data:

SSN (int)
first_name (char)
last_name (char)
middle_name (char)

A sampling of data would appear as such:
000112222, Jones, Jeff, J.
000112222, Jones, Jeff, Jason
111223333, Wright, Kevin, ''
222333444, Jones, Jeff, J.

Knowing that a unique person may have an entry for the same SSN due to variations in the name, I need to find out if there are any instances where the best match of a person's name might be on more than one distinct SSN. In the above example, Jeff Jones appears on one SSN twice due to one record having his middle name spelled out. This is not an issue. However, Jeff Jones also appears on a completely different SSN. This is the discrepancy I need to look for.

Now, I have in the past compared names on the same SSN by using DIFFERENCE() and/or SOUNDEX() to score possible data anomalies, but I'm sort of stuck on checking the names across multiple SSN's to find when a unique person (based on names) have more than one SSN record.
Jan 10 '09 #1
Share this Question
Share on Google+
2 Replies

P: 30
Try using a cursor and go over each table record one at a time selecting the name fields. In the body of the cursor, write a query to retrieve SSN for the selected record if the count(..) >1 or something.
Jan 11 '09 #2

Expert 100+
P: 1,134
Presuming that the last name is always present in full
and at least the first letter of the first name
and at least the first letter of the middle name
is always present

Then something like this should work for you
Expand|Select|Wrap|Line Numbers
  1. select *
  2. from TheTable a
  3. join TheTable b on a.Last_Name=b.Last_Name
  4.                      and left(a.first_name,1)=left(b.first_name,1)
  5.                      and left(a.middle_name,1)=left(b.middle_name,1)
  6.                      and a.ssn<>b.ssn
Jan 12 '09 #3

Post your reply

Sign in to post your reply or Sign up for a free account.