Duplicate Query Search - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Duplicate Query Search

Author

Topic

Cal88
Starting Member

3 Posts

Posted - 2014-06-07 : 20:17:52

I'm trying to do some analysis on duplicate records based off of several match keys. I have a data set of approximately 30,000 people and the goal is to determine how many duplicate matches are in the system.

How would I write an SQL statement that looks for the following pieces of information. (I'm not using one person as an example; I need to do an analysis on the entire data set)

First name (exact match)
Last name (exact match)
Address line 1 (exact match)
Postal code/zip (exact match)

First Initial (exact match)
Last name (exact match)
DOB exact match
Postal code/zip (exact match)

Appreciate the help!

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-06-08 : 06:14:06

Maybe this:

select [First name]
      ,[Last name]
      ,[Address line 1]
      ,[Postal code/zip]
      ,[First Initial]
      ,[DOB]
  from yourtable
 group by [First name]
         ,[Last name]
         ,[Address line 1]
         ,[Postal code/zip]
         ,[First Initial]
         ,[DOB]
 having count(*)>1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources