CODEWITHSUNDEEP
sql-server
lms
lms

Reputation: 93

SQL identify duplicate and update

i need help in below issue.i have a customer table CustA which is having columns custid, first name , surname, phone1, phone2,lastupdateddate. This table has duplicate records.a record is considered duplicate in CustA table when 

first name & surname & (phone1 or phone2) is duplicated  
 custid firstname surname phone1 phone2 lastupdateddate

 1000       Sam      Son      334566   NULL   1-jan-2016
 1001       sam      son      NULL   334566   1-feb-2016

i have used cte for this scenario to Partition by firstname, lastname, phone1, phone2 based on rownumber. But the OR condition is remaining as challenge for phone1 or phone2 in CTE query. Please share your thoughts. Appreciate it.

Upvotes: 1

Views: 61

Answers (2)

John Cappelletti
John Cappelletti

Reputation: 81930

This one will also give you the list of dupes (optional custid<>A.custid)

Declare @Yourtable table (custid int,firstname varchar(50),surname varchar(50),phone1 varchar(25),phone2 varchar(25),lastupdate date) 
Insert into @Yourtable values 
(1000,'Sam','Son'  ,'334566',NULL    ,'1-jan-2016'),
(1001,'sam','son'  ,NULL    ,'334566','1-feb-2016'),
(1003,'sam','son'  ,NULL    ,NULL    ,'2-feb-2016'),
(1002,'Not','ADupe',NULL    ,NULL    ,'1-feb-2016')

Select A.*
      ,B.Dupes 
 From  @YourTable A
 Cross Apply (Select Dupes=(Select Stuff((Select Distinct ',' + cast(custid as varchar(25)) 
                              From @YourTable 
                              Where custid<>A.custid
                                and firstname=A.firstname 
                                and surname  =A.surname 
                                and (IsNull(A.phone1,'') in (IsNull(phone1,''),IsNull(phone2,'')) or IsNull(A.phone2,'') in (IsNull(phone1,''),IsNull(phone2,'')) ) 
                                For XML Path ('')),1,1,'')
                            )
              ) B
 Where Dupes is not null

Returns

custid  firstname   surname phone1  phone2  lastupdate  Dupes
1000    Sam         Son     334566  NULL    2016-01-01  1001,1003
1001    sam         son     NULL    334566  2016-02-01  1000,1003
1003    sam         son     NULL    NULL    2016-02-02  1000,1001

Upvotes: 0

Pரதீப்
Pரதீப்

Reputation: 93694

Trick here is COALESCE

With cte as
(
select Count()over(partition by firstname, lastname, coalesce(phone1, phone2)) as cnt,*
From yourtable
) 
Select * from CTE 
WHere cnt > 1

Though if it isn't the case that one is always null You can use a CASE expression to ensure that the values are presented in a consistent order.

WITH cte
     AS (SELECT COUNT(*)
                  OVER(
                    partition BY firstname, 
                                 lastname, 
                                 CASE WHEN phone1 < phone2 THEN phone1 ELSE phone2 END, 
                                 CASE WHEN phone1 < phone2 THEN phone2 ELSE phone1 END) AS cnt,
                *
         FROM   yourtable)
SELECT *
FROM   CTE
WHERE  cnt > 1

Upvotes: 1

Related Questions