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 in the Desktop version of Atlas.  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