Removing duplicates using sql script in MS CRM 2011

Recently we have a requirement where we have to remove the duplicates records using sql query in MS CRM 2011.

To better understand the requirement let's describe entity and their relationship

Duplicate Entity :AccountMapping
Related entities related to AccountMapping  as below

Having 1:N relationship with AccountMapping

1) CSCall ( One Accountmapping can have multiple CSCalls)

Having N:1 relationship with AccountMapping
1)Account (One  Account can have multiple AccountMapping  )

Problem Screenshot

What we need to achieve

1)We need to remove the duplicate account mappings.
Example :If their are three duplicate Accountmapping having same AccountNumber then we need to delete
two duplicate Accountmapping

Challenges:Difficulty we were facing was that we have to update the references of the to be deleted duplicate AccountMapping to one AccountMapping records.
Example: If we have one account with two duplicate accountmapping(Say AM-1,AM-1) where first AM-1
is  referred by two CSCalls(Say CS-1,CS-2)and second AM-1 is  referred by one CSCall (Say- CS-3).
If  We are deleting the duplicate accountmapping say second AM-1 then we have to programatically
change the reference of CS-3 to first AM-1.

Refer below diagram to know the requirement.

Solution :
Query 1 :Sql script to get the  all Accounts which have duplicate Accountmapping

 

select new_accountid,new_accountidname,new_accountnumber,COUNT(1) as DuplicateRecordCount 
from Filterednew_accountmapping  am
inner join FilteredAccount ac on ac.accountid = am.new_accountid
where new_accountid is not  null ac.statecode =0 and am.statecode =0 
Group By new_accountid,new_accountidname,new_accountnumber
Having COUNT(1) >1

Query 2:Delete duplicates account mapping which do not referenced in CS Calls

begin tran
  
 delete from new_accountmappingExtensionBase
 
 where new_accountmappingid in
 (
 
 Select  distinct 
a.new_accountmappingid
From
(select  new_accountmappingid,A.new_accountid,A.new_accountidname,A.new_accountnumber from Filterednew_accountmapping CS
join(select new_accountid,new_accountidname,new_accountnumber,COUNT(1) as DuplicateRecordCount 
from Filterednew_accountmapping  am
inner join FilteredAccount ac on ac.accountid = am.new_accountid
 where new_accountid is not  null and ac.statecode =0    and am.statecode =0
--where Am.new_accountidname='Village Vet of Urbana'
Group By new_accountid,new_accountidname,new_accountnumber
Having COUNT(1) >1)A
on CS.new_accountid=A.new_accountid And
CS.new_accountidname=A.new_accountidname And
CS.new_accountnumber=A.new_accountnumber)A
left outer Join Filterednew_cscase C
on A.new_accountmappingid=c.new_cscallaccountmappingid 
 where C.new_cscallaccountmappingid  is null
 
 )

Final Query :Making use of above sql queries Query 1, Query2 also we have written a CURSOR which updates the references of CSCalls and deletes the duplicate Accountmapping.

 -------------------------------------------------------------------------------
 -- Delete duplicates account mapping which do not referenced in CS Calls
 -------------------------------------------------------------------------------

begin tran
  
 delete from new_accountmappingExtensionBase
 
 where new_accountmappingid in
 (
 
 Select  distinct 
a.new_accountmappingid
From
(select  new_accountmappingid,A.new_accountid,A.new_accountidname,A.new_accountnumber from Filterednew_accountmapping CS
join(select new_accountid,new_accountidname,new_accountnumber,COUNT(1) as DuplicateRecordCount 
from Filterednew_accountmapping  am
inner join FilteredAccount ac on ac.accountid = am.new_accountid
 where new_accountid is not  null and ac.statecode =0    and am.statecode =0
Group By new_accountid,new_accountidname,new_accountnumber
Having COUNT(1) >1)A
on CS.new_accountid=A.new_accountid And
CS.new_accountidname=A.new_accountidname And
CS.new_accountnumber=A.new_accountnumber)A
left outer Join Filterednew_cscase C
on A.new_accountmappingid=c.new_cscallaccountmappingid 
 where C.new_cscallaccountmappingid  is null
 
 
 )
 
 -------------------------------------------------------------------------------
 -- Update CS Call reference and delete duplicates account mapping
 -------------------------------------------------------------------------------
 --rollback tran
begin tran

DECLARE @Accountid uniqueidentifier, @AccountName nvarchar(100), @AccountNumber nvarchar(100), @AccountMapId uniqueidentifier

DECLARE My_cursor CURSOR FOR 

select new_accountid,new_accountidname
from Filterednew_accountmapping  am
inner join FilteredAccount ac on ac.accountid = am.new_accountid
where new_accountid is not  null and ac.statecode =0
Group By new_accountid,new_accountidname,new_accountnumber
Having COUNT(1) >1

OPEN My_cursor

FETCH NEXT FROM My_cursor INTO @Accountid, @AccountName

WHILE @@FETCH_STATUS = 0
BEGIN

 Select  distinct TOP 1
 @AccountMapId = a.new_accountmappingid, @AccountNumber = a.new_accountnumber  From
 (select  new_accountmappingid,A.new_accountid,A.new_accountidname,A.new_accountnumber from Filterednew_accountmapping CS
 join(select new_accountid,new_accountidname,new_accountnumber,COUNT(1) as DuplicateRecordCount 
 from Filterednew_accountmapping  am
 inner join FilteredAccount ac on ac.accountid = am.new_accountid
 
 where Am.new_accountid=@Accountid and 
 ac.statecode =0 and am.statecode =0
 Group By new_accountid,new_accountidname,new_accountnumber
 Having COUNT(1) >1)A
 on CS.new_accountid=A.new_accountid And
 CS.new_accountidname=A.new_accountidname And
 CS.new_accountnumber=A.new_accountnumber)A
 left outer Join Filterednew_cscase C
 on A.new_accountmappingid=c.new_cscallaccountmappingid
 where C.new_cscallaccountmappingid  is  not null
 
 
 update c Set 
  c.new_cscallaccountmappingid = @AccountMapId   
  From new_cscase c WHERE
  c.new_cscallaccountmappingidname = @AccountNumber and c.new_accountnameid = @Accountid
  
 
  delete from new_accountmappingExtensionBase WHERE
  new_accountid = @Accountid and new_accountmappingid != @AccountMapId
  and new_accountnumber = @AccountNumber

  
 FETCH NEXT FROM My_cursor INTO @Accountid, @AccountName
END 
CLOSE My_cursor;
DEALLOCATE My_cursor;