Dynamics CRM (365) Custom Account Duplicate SQL Report

I ran into a requirement the other day where I had to build a custom SQL report to identify possible duplicate account records in CRM. As anyone who has worked as a CRM admin in any capacity knows, users are very good at completely ignoring your meticulous duplicate detection warnings. This can result in a good chunk of bad data in the form of duplicate accounts. While there are many ways to approach a solution to these requirements, including a new out of the box Account duplicate report in the newest version of Dynamics 365, the SQL query I'm going to demo below has worked very well in identifying duplicates.

For this report source SQL query, I'm going to use a Common Table Expression (CTE) to create a temporary table of sorts to then compare against the existing account records. If you're not familiar, a CTE works similarly to a view except that the result set returned from the expression only exists for the duration the query takes to execute. While there are limitations to using CTEs, such as performance, they can be quite handy in the right circumstance. Read more on CTEs here to get a better picture of their benefits and limitations: Common Table Expressions: TechNet

Begin your query with the following syntax to construct your CTE:

GO
WITH Accounts_CTE --common table expression
AS
(
	SELECT name, accountid, owneridname 
	FROM FilteredAccount
	WHERE statuscode = 1 --active records condition	
)

You need to first define the CTE before you can reference it. My CTE, named "Accoutns_CTE", contains the account name, id and the name of the owner. We will use all of these fields in helping us to identify duplicate account matches in the report data set.

The next step is to construct the SELECT statement and join to the CTE:

SELECT DISTINCT 
  a.name Account_Name
, a.owneridname Owner_Name
, ac.name Matched_Account
, ac.owneridname Matched_Account_Owner_Name 

FROM FilteredAccount a
	INNER JOIN Accounts_CTE ac 
	ON ac.name LIKE '%' + a.name + '%'--use '%' and the LIKE operator to select all accounts with the same name substring in them

We are joining the Account entity (FilteredAccount) to our CTE table (Accounts_CTE) on the account name, specifically where the account name of the CTE record contains the sub string of the Account entity name. This syntax will allow us to select accounts to be returned in the data set where the names are similar without the need for an exact match. Duplicate accounts are often named slightly different, such as "Company Name, Inc." or "Company Name Corp.," and this condition will help us identify those accounts that may be duplicates based on similar naming conventions.

The final step is to add in a WHERE condition which selects only active accounts and ignores accounts from the CTE that are the same. We only need to list out the potential duplicates in our report, so it would be redundant to include exact matches of the compared account by using the account id. You can top off the query with an ORDER BY clause so that all of the potential duplicates are listed sequentially by account name:

WHERE a.accountid != ac.accountid AND a.statuscode = 1

ORDER BY a.name;
GO

We can put the whole query together like this, and then it is ready to be used as a source query for a data set in a custom SSRS report for Dynamics CRM:

GO
WITH Accounts_CTE --common table expression
AS
(
	SELECT name, accountid, owneridname 
	FROM FilteredAccount
	WHERE statuscode = 1 --active records condition	
)

SELECT DISTINCT 
  a.name Account_Name
, a.owneridname Owner_Name
, ac.name Matched_Account
, ac.owneridname Matched_Account_Owner_Name 

FROM FilteredAccount a
	INNER JOIN Accounts_CTE ac 
	ON ac.name LIKE '%' + a.name + '%'--use '%' and the LIKE operator to select all accounts with the same name substring in them

WHERE a.accountid != ac.accountid AND a.statuscode = 1

ORDER BY a.name;
GO

Cheers!

-James Houck

developer/analyst/artist/passioneer