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!
Recommend you do this first in your test environment.
In list manager, first get your price types (in) and productions (has). This gets ID number in place in a good query for your system, but without the count. Save. Then go back into the List you just saved and add the bolded text below exactly where indicated using the manual edit button. Validate, then save with new name. Generate.
Select Distinct a.customer_no
From V_CUSTOMER_WITH_PRIMARY_GROUP a WITH (NOLOCK)
JOIN (Select a1.customer_no From vs_ticket_history a1 WITH (NOLOCK) Where a1.price_type in (<your non-comp price types from the standard drop down criteria>)) as e ON e.customer_no = a.customer_no
Where IsNull(a.inactive, 1) = 1
AND EXISTS (select * from vs_ticket_history WITH (NOLOCK) where a.customer_no = vs_ticket_history.customer_no and vs_ticket_history.prod_no IN (<your production numbers from the standard drop down criteria>) having count (distinct vs_ticket_history.prod_no) >=3)
(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