Constituents who bought tickets to more than one performances on the same day

Hi Susan,

I guess it depends on what you want to do with this information.  However, you could build a list - the criteria in my screen grab shows how I would generate a list for everyone that has tickets for more than one performance on 21 August.

We use mail2 for our performance reminders, sending the reminder the day before the performance/s but that contains the details of the multiple performances being attended on that day.  I created a database view that would retrieve the performance details - happy to share!

Martin 

Thanks so much for the response, Martin. That criteria for the list is exactly what we need but for the last 365 days(!). Since we have so many performances (roughly 150/week), we don't send out reminders. Back to the now better-lit drawing board.

Susan

Sorry Susan, I'm not sure I understand what it is you're trying to identify - is the basis of your query the performance date or the transaction date?.  Rereading both comments, it sounds like you're trying to identify either:

• people that in the last 365 days have bought tickets to more than one performance on the same performance date (regardless of transaction date)
• people that in the last 365 days have bought tickets to more than one performance on the same transaction date (regardless of the performance date)

Either way, I would be happy to try and help!

Martin

Thanks for the response, Martin. The first example is what we're looking for. 

Thanks for the response, Martin. The first example is what we're looking for. 

Well, sorry to say I don't know that there's an elegant way in List Manager to do this.  However, if List Manager allows you the Manual Edit option, you could drop this code into the editor window:

Select Distinct a.customer_no
From V_CUSTOMER_WITH_PRIMARY_GROUP a WITH (NOLOCK)
JOIN (Select hv.customer_no From vs_ticket_history a1 WITH (NOLOCK)
JOIN V_CUSTOMER_WITH_HOUSEHOLD hv ON a1.customer_no = hv.expanded_customer_no
Where a1.perf_dt >= getdate() - 365
Group By hv.customer_no, convert(varchar(25), a1.perf_dt, 101)
Having count(distinct a1.perf_no) >= 2) as e ON e.customer_no = a.customer_no
Where IsNull(a.inactive, 1) = 1

This code searches households that have purchased tickets to more than one performance where the performance date is in the last 365 days.

Hope this helps - if not - just shout out!

Martin 

I have done some things like this myself a number of times, and it does work well.  One caveat is that if you ARE part of a consortium, the SQL will need to be modified a bit, assuming you are looking for your organization only and not campus/consortium numbers.

Hi John,

My code snippet should work within a consortium environment as the code only references the standard, secure view that limits the user to only be able to investigate the productions/relationships enabled to their control grouping.

Martin

I forgot that all consortia are not set up like ours.  We have some very strange things going on in how our control groups are set up in that access for some departments is shared across organizations despite being separate entities, so relying on just view security will not always work for us.

For example, one of our organizations is integrated with ours to the point where we share the same website and have full access to each other's data and yet the idea of a "first time" constituent or a constituent who has purchased "more than one performance on the same day" would still be specific to the organization.  Also, our Box Office would not be able to pull this list accurately as they have access to ALL the data for all organizations since all ticket orders are processed through the one Box Office.

But, as I said, I forgot, and I suppose that is just our uniqueness rearing its head again.

Brilliant! It works. Thanks so much, Martin.