List of Donors who attend lots of shows

Allegra Thompson $organization over 4 years ago

Hi all,

I've been hunting around and I'm not finding exactly what I'm looking for. Hoping someone can put me out of my misery!

I want to generate a list of constituents with a total giving of $500 in a given year who also attended 10+ events in that year. I've tried using the Ticket History Unique Perfs criteria, but it's spitting out constituents with 10+ total tickets, not 10+ concerts/performances.

Is there something I'm missing?

Heath Wilder $organization over 4 years ago

Hey Allegra,

I actually gave this a shot in SQL the other day and then got pulled into a mountain of other work. Check and see if this helps at all. You should be able to paste it into a show query field in a new list. NB/ this count performance but you could count Production Season etc. also groups contribution by fiscal year rather than campaign.

Select distinct customer_no 
from T_CUSTOMER with (NOLOCK)
where Exists 
	(
		select 
			customer_no,
			sum(recd_amt) year_giving_total,
			cm.fyear
		from T_CONTRIBUTION c with (NOLOCK)
		Join T_CAMPAIGN cm with (NOLOCK)
			on c.campaign_no = cm.campaign_no
		where T_CUSTOMER.customer_no = c.customer_no
		group by customer_no, fyear
		having sum(recd_amt) > 500
	
	) 
and exists 
	(
		select 
		customer_no
		from VS_TICKET_HISTORY with (NOLOCK)
		where T_CUSTOMER.customer_no =  VS_TICKET_HISTORY.customer_no
		group by customer_no
		having count(distinct perf_no) > 10
		
	)