Any ideas on how to find the number of people who purchased tickets for more than one performance in the same day? We're an Art House Cinema, so this isn't as rare as it might be for other venues. Any suggestions would be appreciated!
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:
Either way, I would be happy to try and help!
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!
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.
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.