Search for duplicate accounts

Question

I've been beating my head for two days and I finally concede that I need help doing this seemingly simple task.

I'm trying to find a way to produce a list of possible duplicate accounts in a single table. Fields are similar to AccountNumber, FirstName, LastName, DOB, SSN, Address, City, State, Zip.

I need to find a way to query the DB and find accounts that have different AccountNumbers but similar names/DOB/etc that are likely the same person.

Any help would be greatly appreciated. Thanks!

Answer 1

I got a query from our software vendor that did exactly what I needed it to do. Their solution is kind of a combination of the two suggestions above. It creates a temp table to put accounts that have more than one firstname lastname matching, then rates the likelihood that they are duplicates by checking for matching address, state, city. It also notes the last time each account had been used.

I don't fully understand the syntax, but that's ok, it gets the job done.

Thanks for the help!

Answer 2

Look at using recursive selects. Here's an article on it. http://technet.microsoft.com/en-us/library/ms186243(v=sql.105).aspx

Basically, it creates a temporary table that allows you to perform actions on without requerying the database directly. This allows you to perform multiple sub selects more efficiently.

Your query would end up looking something like the following.

With accounts as (select a, b, c from table)

Select a, b, c From table tbl Where exists (select 1 from accounts act where act.a like '%' + tbl.a + '%') ...

Etc for more information on how to check if a column is like another check this out Compare Columns Where One is Similar to Part of Another

Answer 3

select distinct t1.AccountNumber
from table t1
join table t2 on t2.Name = t1.Name and t2.DoB = t1.DoB
    and t2.AccountNumber <> t1.AccountNumber