Event Timeline
Comment Actions
I've restored the email data examples. However, digging reveals 100 Major Gifts donors who have potentially lost their addresses. Only 2 are recent - rest are all 2016 & addresses are visible on the history tab - will discuss with MG
SELECT retained_contact.contact_id, count(a.id) as count_rows,
-> e.street_address , e.location_type_id, e.is_primary, deleted_contact.contact_id as deleted_contact_id, -> e.log_conn_id, a.activity_date_time -> # We are looking for duplicate merge activities at exactly the same time -> FROM civicrm_activity a -> # restrict to merge activities with merged-to contacts (these are retained) -> INNER JOIN civicrm_activity_contact retained_contact ON activity_id = a.id AND a.activity_type_id = 87 AND retained_contact.record_type_id = 1 -> # join merge activity to contact merged from -> INNER JOIN civicrm_activity_contact deleted_contact ON deleted_contact.activity_id = a.id AND deleted_contact.record_type_id =3 -> # join to activity log table on the insert record to get the connection -> LEFT JOIN log_civicrm_activity la ON la.id = a.id AND la.log_action = 'Insert' -> # join that connection onto the deleted record in the email log table -> INNER JOIN log_civicrm_address e ON e.log_conn_id = la.log_conn_id AND e.log_action = 'Delete' -> # join that record onto the retained contact's existing email - we want ones where they have no email( AT ALL) -> LEFT JOIN civicrm_address address ON address.contact_id = retained_contact.contact_id -> WHERE address.id IS NULL -> AND e.street_address IS NOT NULL -> # group by activity time, looking for more than one record created at the exact same time. -> GROUP BY a.activity_date_time, retained_contact.contact_id , deleted_contact.contact_id -> HAVING count_rows > 1;
Comment Actions
@MBeat33 I restored the data - is this OK to close? Will open follow up for MG records