SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Duplicate Query Search
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Cal88
Starting Member

3 Posts

Posted - 06/07/2014 :  20:17:52  Show Profile  Reply with Quote
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
Constraint Violating Yak Guru

358 Posts

Posted - 06/08/2014 :  06:14:06  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000