Find Duplicate Organizations / Duplicate Profiles

Issue: I want to be able to find all duplicate profiles

Resolution: The SQL below will assist you in  finding all the profiles that are duplicates.  Copy and paste code below under Reports > SQL Writer Tool.  For more information on the SQL writer, click here.


Find Duplicate Organizations: 

SELECT DupCheck.rn [DupNo]
,[Profile].ProfileID
,CASE
WHEN [Profile].OrgName = ''
THEN '(none)'
ELSE [Profile].OrgName
END OrgName
,[Profile].Address1
,[Profile].Address2
,[Profile].City
,[Profile].STATE
,[Profile].Zip
,[Profile].ZipExt
,[Profile].WorkPhone
,[Profile].Fax
,[Profile].Email
FROM dbo.PROFILE
JOIN (
SELECT ROW_NUMBER() OVER (
ORDER BY [Profile].OrgName
) rn
,OrgName
,COUNT(ProfileID) [Count]  FROM dbo.[Profile]  WHERE([OrgName] != ''
AND [OrgName] != 'NULL'
AND [OrgName] IS NOT NULL)  GROUP BY [OrgName]  HAVING COUNT(ProfileID) > 1
) DupCheck ON DupCheck.OrgName = [Profile].OrgName
ORDER BY [Profile].OrgName

 

Find Any Duplicate Profiles: 

SELECT DupCheck.rn [DupNo]
,[Profile].ProfileID
,CASE
WHEN [Profile].ReportName = ''
THEN '(none)'
ELSE [Profile].ReportName
END ReportName
,[Profile].Address1
,[Profile].Address2
,[Profile].City
,[Profile].STATE
,[Profile].Zip
,[Profile].ZipExt
,[Profile].WorkPhone
,[Profile].Fax
,[Profile].Email
FROM dbo.PROFILE
JOIN (
SELECT ROW_NUMBER() OVER (
ORDER BY [Profile].ReportName
) rn
,ReportName
,COUNT(ProfileID) [Count]
FROM dbo.[Profile]
WHERE (
[ReportName] != ''
AND [ReportName] != 'NULL'
AND [ReportName] IS NOT NULL
)
GROUP BY [ReportName]
HAVING COUNT(ProfileID) > 1
) DupCheck ON DupCheck.ReportName = [Profile].ReportName
ORDER BY [Profile].ReportName

Comments

1 comment
  • I ran the find duplicate organizations and at first glance it looked like a number of duplicate organizations were identified, but when I looked up the profiles very often only one profile was actually an organization and the duplicate identified was a related individual profile not an organization.
    Wish the list was actually only organizations as I had expected it to be.

    0
Want some training on WebLink Connect? Sign up today for one of our upcoming online training classes.