Email Addresses Updated Last 30 Days
This View shows Contacts where an email address has been updated in the last 30 days.
CAUTION: This view example is offered as is and may need to be altered to fit your specific church culture. For assistance, contact your SPoC or, if you are a SPoC, contact Support for a Professional Services estimate (billed at an hourly rate).
Difficulty: Advanced
Purpose: Creates list of Contacts with an email address that was edited in the past 30 days.
Page: Contacts
Field List
Contacts.[Display_Name] AS [Display Name],
Contact_Status_ID_Table.[Contact_Status] AS [Contact Status], Participant_Record_Table_Participant_Type_ID_Table.[Participant_Type] AS [Participant Type],
Contacts.[__Age] AS [Age],
Contacts.[Email_Address] AS [Current Email Address],
(SELECT TOP 1 Previous_Value from dp_Audit_Detail AD
JOIN dp_Audit_Log AL ON AD.Audit_Item_ID = AL.Audit_Item_ID
WHERE AL.Table_Name = 'Contacts' AND AL.Record_ID = Contacts.Contact_ID AND AL.Date_Time >= GETDATE() - 30
AND AD.Field_Name = 'Email_Address') AS [Previous Email],
(SELECT TOP 1 New_Value from dp_Audit_Detail AD
JOIN dp_Audit_Log AL ON AD.Audit_Item_ID = AL.Audit_Item_ID
WHERE AL.Table_Name = 'Contacts' AND AL.Record_ID = Contacts.Contact_ID AND AL.Date_Time >= GETDATE() - 30
AND AD.Field_Name = 'Email_Address') AS [New Email],
(SELECT TOP 1 User_Name FROM dp_Audit_Log AL
JOIN dp_Audit_Detail AD ON AL.Audit_Item_ID = AD.Audit_Item_ID
WHERE AL.Table_Name = 'Contacts' AND AL.Record_ID = Contacts.Contact_ID AND AL.Date_Time >= GETDATE() - 30
AND AD.Field_Name = 'Email_Address' ) AS [Changed By],
(SELECT TOP 1 Date_Time FROM dp_Audit_Log AL
JOIN dp_Audit_Detail AD ON AL.Audit_Item_ID = AD.Audit_Item_ID
WHERE AL.Table_Name = 'Contacts' AND AL.Record_ID = Contacts.Contact_ID AND AL.Date_Time >= GETDATE() - 30
AND AD.Field_Name = 'Email_Address' ) AS [Date Changed], Contacts.[Bulk_Email_Opt_Out] AS [Bulk Email Opt Out], Contacts.[Email_Unlisted] AS [Email Unlisted]CopiedView Clause
EXISTS (SELECT 1 FROM dp_Audit_Log AL JOIN dp_Audit_Detail AD ON AL.Audit_Item_ID = AD.Audit_Item_ID WHERE AL.Table_Name = 'Contacts' AND AL.Record_ID = Contacts.Contact_ID AND AL.Date_Time >= GETDATE() - 30 AND AD.Field_Name = 'Email_Address' )Order By
[Date Changed] DESCTechniques
- INNER JOIN
- GETDATE
- SELECT TOP 1