Skip to content

Instantly share code, notes, and snippets.

@ginkgomzd

ginkgomzd/Find Suspect Memberships

Created November 2, 2016 22:26
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save ginkgomzd/20d1c83fbdf8b698009eedb262b63de1 to your computer and use it in GitHub Desktop.
Save ginkgomzd/20d1c83fbdf8b698009eedb262b63de1 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
Find Suspect Memberships
-- Find suspect Memberships due to contributions being linked to multiple membership-payment records
SELECT
mempay.id AS mempay_id,
mempay.contribution_id AS contrib_id,
contrib.receive_date, contrib.total_amount,
mempay.membership_id AS mem_id, memtype.name,
c_contrib.display_name AS contrib_contact_name, c_contrib.id AS contrib_contact_id,
c_mem.id AS mem_contact_id, c_mem.display_name AS mem_contact_name
FROM
civicrm_membership_payment mempay
LEFT JOIN civicrm_membership mem ON mempay.membership_id=mem.id
LEFT JOIN civicrm_membership_type memtype ON mem.membership_type_id=memtype.id
LEFT JOIN civicrm_contribution contrib ON mempay.contribution_id=contrib.id
LEFT JOIN civicrm_contact c_mem ON mem.contact_id=c_mem.id
LEFT JOIN civicrm_contact c_contrib ON contrib.contact_id=c_contrib.id
WHERE mempay.contribution_id
IN ( SELECT contribution_id FROM civicrm_membership_payment GROUP BY contribution_id HAVING count(*)>1 )
ORDER BY contrib_id, mempay.id
;
@davejenx
Copy link

This will also show legitimate contributions where the contribution is for more than one membership.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment