Below is a collection of useful SQL queries designed to support data cleanup and provide valuable insights. These queries can help you better understand and manage your data within the system.
For guidance on how to run SQL queries, please refer to the following resource: https://atlas.memberclicks.com/hc/en-us/articles/14750238429325-Custom-SQL-Queries
All Individual Profiles Without Relationship or With Null Relationship
Select Profile_View_Profile_ProfileId, Profile_View_Profile_ReportName, Profile_View_Profile_OrgInd,ProfileRelations_RelProfileID
From ProfileRelations_View
Where Profile_View_Profile_OrgInd = 0 and ProfileRelations_RelProfileID = 0
Duplicate Email Addresses
SELECT Profile_View.Profile_Email
FROM Profile_View
GROUP BY Profile_View.Profile_Email
HAVING COUNT(Profile_View.Profile_Email) > 1
Duplicate Profiles by Report Name Match
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
Duplicate Organization Profile by Report Name Match
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
Non-Members with Active Listing
SELECT
Profile_ProfileId AS 'Profile ID',
Profile_ReportName AS 'Profile Report Name',
MembershipLevel_Name AS 'Membership Level Name',
Profile_CustStatus AS 'Profile Status',
Profile_AnnualDues AS 'Profile Annual Dues',
ListingCategory_Category AS 'Category',
ListingSubCategory_SubCategory AS 'Sub-Category',
StandardCategory_StandardCategory AS 'Standard Category',
ListingType_ListingType AS 'Listing Type',
ListingTypeDetails_ListingType AS 'Detail Listing Type'
FROM
Listing_View
WHERE
Profile_Member = 0
AND Listing_Active = 1
ORDER BY
Profile_ReportName ASC