I am trying to pull a list of constituents who have purchased 3 or more productions out of 9 specific productions. These need to be PAID orders, not comps of any kind. These constituents can have ANY combination grouping of 3 of the 9 productions. Any help is appreciated. This is cross posted in other forums as well.
For Example, out of the multiple possibilities of 9 productions, I would want to see a constituent record with purchases to production 1, 4, and 9; or 2, 3, and 7, etc.
Drat.
Sorry – thought that might do it for you.
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of David Anderson Sent: Thursday, November 3, 2016 9:14 AM To: Michelle Usadel <musadel@phoenixsymphony.org> Subject: Re: [Tessitura Technical Forum] 3 or More Productions
Hi Michelle-
Wish I could say that worked. Yes, it pulls patrons with 3 or more shows, however it returns results based on them having at least one of the nine performances and two or more other shows, not people that have 3 or more of the selected 9.
From: Michelle Usadel <bounce-michelleusadel3057@tessituranetwork.com> Sent: 11/2/2016 7:34:30 PM
Hi Marie,
You should be able to do this by using the list criteria "tickets - number of unique performances". As your first criteria add the nine performance you want included and then use >= 3 as your unique performances criteria.
I tested it out on our Specials which aren't part of a package and it worked - giving me results that only had 3 or more of those selected performance. Some of the results were people who had included those performances in a CYO package.
If you just want single ticket buyers, you may need to use a MOS criteria also.
Hope that helps!
Michelle
This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!
(duplicate post)
Aha, I missed the part about screening by price type above, but I believe this will not work: it will give you someone who has bought tickets with a non-comp price type (to any show ever), who has also bought tickets 3 or more of the productions, possibly all with comps.
So, maybe like this
select distinct vc.customer_no from V_CUSTOMER_WITH_PRIMARY_GROUP as vc with (nolock) inner join ( select distinct th.customer_no from VS_TICKET_HISTORY as th with (nolock) inner join TR_PRICE_TYPE as pt on pt.id = th.price_type where th.prod_no in (2101,4209,99,1093) --sample of production #s on our system and pt.price_type_category <> 4 --comp price type category on our system group by th.customer_no having COUNT(distinct th.prod_no) > 2 ) as p3 on p3.customer_no = vc.customer_no