Wednesday, May 27, 2009

CRM: Using SQL

A client asked: Can SQL be used with CRM to find duplicate records as part of cleaning up the database?

Yes, below is a SQL query that will find duplicate leads. It can also be used to find duplicate contacts with a few modifications.

To use this query, open the SQL Management Studio and select your MSCRM database. Click New Query and make sure your MSCRM database appears in the drop down box. Enter the query below and click Execute. It should return any duplicates in the Results pane:

SELECT firstname, lastname, address1_city AS City, LEFT(firstname, 4) + LEFT(lastname, 4) + LEFT(address1_city, 5) AS Dup_Add, COUNT(*) AS Num
FROM FilteredLead
GROUP BY firstname, lastname, address1_city, statecode
HAVING (COUNT(*) > 1) AND (NOT (firstname IS NULL)) AND (NOT (lastname IS NULL)) AND (statecode = N'0')
ORDER BY lastname

These duplicate records can then be reviewed and either modified or deleted.

No comments: