Find folks who have started a new recurring
ActivePublic
Actions

Authored by Ejegg on Mar 8 2024, 12:17 AM.

Tags

None

Referenced Files

	F42451324: Find folks who have started a new recurring
	Mar 8 2024, 12:17 AM

Subscribers

Eileenmcnaughton

	select cr.* from contribution_recur_copy crc
	inner join civicrm_email e on e.contact_id = crc.contact_id
	left join civicrm_email e2 on e2.email = e.email and e2.contact_id <> e.contact_id
	inner join civicrm_contribution_recur cr ON ( cr.contact_id = e.contact_id OR cr.contact_id = e2.contact_id )
	WHERE cr.cancel_date IS NULL
	and ( cr.end_date IS NULL OR cr.end_date > now() )
	AND cr.contribution_status_id in (1,3,4);

Event Timeline

Ejegg created this paste.Mar 8 2024, 12:17 AM

So I tried working it in this way

select cr.contribution_status_id, COUNT(DISTINCT e2.email),e2.email, cancel_date,end_date from contribution_recur_copy crc   inner join civicrm_email e on e.contact_id = crc.contact_id   left join civicrm_email e2 on e2.email = e.email   inner join civicrm_contribution_recur cr ON ( cr.contact_id = e.contact_id OR cr.contact_id = e2.contact_id ) WHERE cr.cancel_date IS NULL GROUP BY contribution_status_id, cancel_date;

``

& found 2 rows - but 0 once I added your extra criteria

The real issue was with the status - should have been 'NOT IN' rather than 'IN'

Find folks who have started a new recurringActivePublicActions

Event Timeline

Find folks who have started a new recurring
ActivePublic
Actions