Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Identify Duplicate Records according to Multiple Criteria Expand / Collapse
Author
Message
Posted Saturday, November 30, 2013 2:32 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 1, 2014 8:32 PM
Points: 21, Visits: 275
Hi There!
I have an Employee table with Columns:

EmpID | FirstName | MiddleName | LastName | SSN | BirthDate | HireDate | City | Zip


Requirement: I have to identify the duplicate records based on below criteria:
Criteria1: SSN
Criteria2: FirstName, LastName, BirthDate
Criteria2: FirstName, LastName, City, Zip

Please tell how to accomplish this task........Thanks in Advance
Post #1518638
Posted Saturday, November 30, 2013 7:29 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 29, 2014 11:29 PM
Points: 147, Visits: 587
Hi,
do you want to accomplish all three conditions in single query or you need three different queries.




Praveen D'sa
MCITP - Database Administrator 2008
http://sqlerrors.wordpress.com
Post #1518646
Posted Saturday, November 30, 2013 9:54 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:22 AM
Points: 36,767, Visits: 31,223
Sy-1148362 (11/30/2013)
Hi There!
I have an Employee table with Columns:

EmpID | FirstName | MiddleName | LastName | SSN | BirthDate | HireDate | City | Zip


Requirement: I have to identify the duplicate records based on below criteria:
Criteria1: SSN
Criteria2: FirstName, LastName, BirthDate
Criteria2: FirstName, LastName, City, Zip

Please tell how to accomplish this task........Thanks in Advance


You'll get much more help if you show what you've tried.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1518678
Posted Monday, December 2, 2013 5:46 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 7:48 AM
Points: 69, Visits: 457
Sy-1148362 (11/30/2013)
Hi There!
I have an Employee table with Columns:

EmpID | FirstName | MiddleName | LastName | SSN | BirthDate | HireDate | City | Zip


Requirement: I have to identify the duplicate records based on below criteria:
Criteria1: SSN
Criteria2: FirstName, LastName, BirthDate
Criteria2: FirstName, LastName, City, Zip

Please tell how to accomplish this task........Thanks in Advance


A search on this site would show you quite a few ways to identify duplicates.
The use of ROW_NUMBER or GROUP BY being a couple that come to mind.
With your criteria specified multiple times though you're not quite clear on how you would like to handle this.
Maybe posting what you've tried as has been suggested would give an idea of what you are actually trying to do.
Post #1518862
Posted Monday, December 2, 2013 6:59 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:51 AM
Points: 7,127, Visits: 13,503
Here's the simplest way. Without further information it's unlikely to be the best.
-- I have an Employee table with Columns:
DROP TABLE #Employee
CREATE TABLE #Employee (EmpID INT IDENTITY(1,1), FirstName VARCHAR(100), MiddleName VARCHAR(100), LastName VARCHAR(100),
SSN VARCHAR(25), BirthDate DATE, HireDate DATE, City VARCHAR(100), Zip VARCHAR(10))

INSERT INTO #Employee (FirstName, MiddleName, LastName, SSN, BirthDate, HireDate, City, Zip)
SELECT 'John', NULL, 'Smith', 'Smooth bitter', GETDATE(), GETDATE(), 'London', 'W1' UNION ALL
SELECT 'Timothy', NULL, 'Taylor', 'Landlord', GETDATE(), GETDATE(), 'London', 'W1' UNION ALL
SELECT 'Timothy', NULL, 'Taylor', 'Y', GETDATE()+1, GETDATE()+1, 'London', 'W1' UNION ALL
SELECT 'Timothy', NULL, 'Taylor', 'X', GETDATE(), GETDATE(), 'P', 'Q' UNION ALL
SELECT 'X', NULL, 'Y', 'Landlord', GETDATE(), GETDATE(), 'Z', 'A'

-- Requirement: I have to identify the duplicate records based on below criteria:
SELECT
EmpID, FirstName, MiddleName, LastName, SSN, BirthDate, HireDate, City, Zip,
Criteria1, Criteria2, Criteria3
FROM (
SELECT
EmpID, FirstName, MiddleName, LastName, SSN, BirthDate, HireDate, City, Zip,
Criteria1 = COUNT(*) OVER(PARTITION BY SSN),
Criteria2 = COUNT(*) OVER(PARTITION BY FirstName, LastName, BirthDate),
Criteria3 = COUNT(*) OVER(PARTITION BY FirstName, LastName, City, Zip)
FROM #Employee
) d
WHERE Criteria1+Criteria2+Criteria3 > 3




“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1518885
Posted Monday, December 2, 2013 8:36 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:22 AM
Points: 36,767, Visits: 31,223
ChrisM@Work (12/2/2013)
Here's the simplest way. Without further information it's unlikely to be the best.
-- I have an Employee table with Columns:
DROP TABLE #Employee
CREATE TABLE #Employee (EmpID INT IDENTITY(1,1), FirstName VARCHAR(100), MiddleName VARCHAR(100), LastName VARCHAR(100),
SSN VARCHAR(25), BirthDate DATE, HireDate DATE, City VARCHAR(100), Zip VARCHAR(10))

INSERT INTO #Employee (FirstName, MiddleName, LastName, SSN, BirthDate, HireDate, City, Zip)
SELECT 'John', NULL, 'Smith', 'Smooth bitter', GETDATE(), GETDATE(), 'London', 'W1' UNION ALL
SELECT 'Timothy', NULL, 'Taylor', 'Landlord', GETDATE(), GETDATE(), 'London', 'W1' UNION ALL
SELECT 'Timothy', NULL, 'Taylor', 'Y', GETDATE()+1, GETDATE()+1, 'London', 'W1' UNION ALL
SELECT 'Timothy', NULL, 'Taylor', 'X', GETDATE(), GETDATE(), 'P', 'Q' UNION ALL
SELECT 'X', NULL, 'Y', 'Landlord', GETDATE(), GETDATE(), 'Z', 'A'

-- Requirement: I have to identify the duplicate records based on below criteria:
SELECT
EmpID, FirstName, MiddleName, LastName, SSN, BirthDate, HireDate, City, Zip,
Criteria1, Criteria2, Criteria3
FROM (
SELECT
EmpID, FirstName, MiddleName, LastName, SSN, BirthDate, HireDate, City, Zip,
Criteria1 = COUNT(*) OVER(PARTITION BY SSN),
Criteria2 = COUNT(*) OVER(PARTITION BY FirstName, LastName, BirthDate),
Criteria3 = COUNT(*) OVER(PARTITION BY FirstName, LastName, City, Zip)
FROM #Employee
) d
WHERE Criteria1+Criteria2+Criteria3 > 3




I could certainly be wrong but this seemed to be a school assignment which is why I wanted the OP to show what has been tried.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1518917
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse