My marketing committee would like me to pull the number of constituents who only bought tickets to a single performance over the last three years. In other words, I'm looking for people who did not attend any other performance within the same season.
Luckily we only produce three mainstage shows a season, but I'm blank in figuring out the easiest way to find this.
Any help would be appreciated.
Brad
Hello,
You could use list manager and put something like the following in the manual edit field.
select customer_nofrom t_lineitem ljoin t_order o on o.order_no=l.order_nojoin t_perf p on p.perf_no =l.perf_nojoin tr_season s on s.id=p.season and s.fyear in (2009,2010,2011)group by customer_nohaving count('x')=1
Brad,
If you pull a list using the "ticket date between xx/xx/xxxx and xx/xx/xxxx" and "tickets-number of unique performances = 1" it should give you people who have purchase tickets to 1 show between the given dates.
Here is the query (if you use the manual edit button and use this query all you need to change are the dates.)
Select Distinct a.customer_no From t_customer a (NOLOCK) Where IsNull(a.inactive, 1) = 1 AND Exists (select 1 from lt_tkt_hist (NOLOCK) Where lt_tkt_hist.customer_no = a.customer_no and perf_dt between '2007/08/01' AND '2010/04/22 23:59:59' Having count(distinct convert(char(12), perf_dt) + convert(char(12),time_slot)) = 1)