This View shows Contacts where an email address has been updated in the last 30 days.
Difficulty: Advanced
Purpose: Creates list of Contacts with an email address that was edited in the past 30 days.
Page: Contacts
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]
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' )
[Date Changed] DESC