CODEWITHSUNDEEP
sqlsql-server
K1205
K1205

Reputation: 85

MSSQL Server - Finding Duplicates on more than one field

I'm trying to find duplicate parties where the gtid is unique but there is duplicate records where the first, last name and DOB are duplicated. So for example there can be multiple records for the following :

gtid : 105 but there should not be more than 1 record for the following example:

gtid : 105, first name : john, last name : smith , dob: 12/05/1992
gtid : 105, first name : john, last name : smith, dob: 12/05/1992

Acceptable : 

gtid : 105, first name : john, last name : smith , dob: 12/05/1992
gtid : 106, first name : john, last name : smith, dob: 12/05/1992

I have the following but it doesn't work : 

SELECT DISTINCT GtId, CrmPartyId, LegalName, BusinessClass, RmFullName, PbeFullName, OverallClientStatus, OverallRpStatus,

FirstName + ' ' + LastName FullName, Dob FROM Core.WeccoParty

Upvotes: 1

Views: 70

Answers (4)

Fahmi
Fahmi

Reputation: 37473

FIRST_NAME + ' ' + LAST_NAME should be in group by:

 SELECT 
        FIRST_NAME + ' ' + LAST_NAME  AS NAME, BIRTH_DATE
    FROM
        Staging.WECCO_Party_In
    GROUP BY
    FIRST_NAME + ' ' + LAST_NAME, BIRTH_DATE
    HAVING COUNT(FIRST_NAME + ' ' + LAST_NAME+' '+BIRTH_DATE) > 1

Upvotes: 0

MAHESH PARAB
MAHESH PARAB

Reputation: 1

You have to use same clause in group by from distinct clause, try following query

SELECT DISTINCT gtid,
    FIRST_NAME + ' ' + LAST_NAME  AS NAME, BIRTH_DATE, COUNT(*)
FROM
    Staging.WECCO_Party_In
GROUP BY
gtid,FIRST_NAME + ' ' + LAST_NAME, BIRTH_DATE
HAVING 
    COUNT(*) > 1

Upvotes: 0

Paweł Dyl
Paweł Dyl

Reputation: 9143

To COUNT gdid remove it from GROUP BY clause:

SELECT FIRST_NAME + ' ' + LAST_NAME Name, BIRTH_DATE, COUNT(*) TotalGdids
FROM Staging.WECCO_Party_In
GROUP BY FIRST_NAME + ' ' + LAST_NAME, BIRTH_DATE
HAVING COUNT(*)>1

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269445

You don't want gtid in the GROUP BY:

SELECT FIRST_NAME, LAST_NAME, BIRTH_DATE,
       MIN(gtid), MAX(gdid), COUNT(*)
FROM Staging.WECCO_Party_In
GROUP BY FIRST_NAME, LAST_NAME, BIRTH_DATE
HAVING COUNT(*) > 1

Upvotes: 1

Related Questions