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;
