I am trying to find patrons who have a ticket to every concert in our orchestral season. They would have to have multiple subs in order to accomplish this as there is no package that includes them all. Is there a why to do this with an extraction?
I already tried finding it with the a script, but I came up with 6 patrons and was told that can't be correct. I queried the vs_ticket_history view, and filtered by season.
Ashley Elliott
Database Administrator
St. Louis Symphony Orchestra
314-286-4198
ashleye@slso.org
I tried IN and HAS and both gave me this error:
What does Show Query produce?
I believe the error is due to too many selections for the one criteria. You might be able to use the secondary criteria to avoid the issue.
That might break the query, can't remember on the top of my head how those sort out, but I think it might turn into a functional OR.
Hi Ashley,
for a ticket in every concert in our orchestral season ... I'm not sure how you are set up but in case a Concert can be grouped in Production Season I'd use HAS Ticketing > Prod Season and a new criteria line for each of these Prod Seasons to get the ""Also has"
I hope I understand that correctly. I'm thinking that Concert doesn't equal Performance but I could be wrong.
Try with a couple of seasons and spot check the results to see if that's working.
Thank you!
Thank you both for your replies! I ended up using HAS and adding a criteria line for each production. I learned that you can only get about 19 separate criteria lines in a List Manager list, so at that point I copied the query into SSMS and added the rest.
My result was 6 patrons - the same number I got when I queried vs_ticket_history view. I think my number is correct because I got the same number from two different methods (the list manager query used vw_sls_cust_perf_pricetype by the way). My coworkers still say there should be 30 or so patrons.
Thanks for all your help!
Ashley
Can they show you a customer not on the list who meets the criteria?
Funny you should ask! As it turns out I was correct. Patrons with a Saturday A, Saturday B, Saturday C, and Saturday D package is what equaled 30.
I literally found everyone who had a ticket to all 24 productions and that was 6. Apparently in previous years adding up people who have Sat A, B, C, and D would have produced those patrons - people who have a ticket to every production in the season. However, this is going to be my second season with this org so I didn't know that fact about Sat packages, AND this year there is one classical concert that doesn't fall into any of the Saturday packages because of Halloween.
And that is why they need you around!
Ha!
I truly appreciate your help Gawain.
I'm glad to see that you were able to get this figured out! Just for future reference, I wanted to summarize a few things about how criteria sets work when it comes to AND vs OR and throw in a link to some video resources to help illustrate the concepts.
When you make multiple selections from a criteria value dropdown, they are always joined by OR, meaning a constituent has to have just one of the values in the dropdown but not all of the values. When you are looking for someone who has all the values, you have to add the criterion multiple times to the set, using HAS as the operator, and making just one selection from the dropdown for each row.
You can find a video training series on lists and criteria here: https://www.tessituranetwork.com/Support/Training/Intro-Courses/Lists-and-OutputLessons 3 and 4 cover how criteria work together and the differences between IN and HAS.
Good job getting your query sorted, and thanks Gawain and Heath for being such helpful neighbors (in this and many other forum posts).
-Kevin