Hello,
We did a promotion in which subscribers who bought single tickets in addition to their subscription will receive some free coupons for parking. They get one coupon per single concert they bought. I am having trouble finding this information.
I tried to make an extraction, but I can't yield the results I am looking for. I need to be able to break it down by subs who bought 5 or more single concerts, subs who bought 4 single concerts, 3, 2 and 1.
Anybody have any suggestions for getting these 5 different groups? We don't have T-stats yet.
Thanks in advance!
I made a list parameter called Tickets-Ticket Hist Price Type that helped me in List Manager create a query that will give you what you want, I think. Paste the following into the Graphical Edit window of a list, replacing “46” (it appears twice) with the season you are interested in, and the final “2” with the number of concerts you are interested in for this list:
Select Distinct a.customer_no
From t_customer a (NOLOCK)
JOIN vs_subscription_hist e (NOLOCK) ON a.customer_no = e.customer_no
Where IsNull(a.inactive, 1) = 1
AND e.season in (46)
AND Exists (select 1 from vs_tck_hist (NOLOCK) Where vs_tck_hist.customer_no = a.customer_no and price_type = 'I' and season in (46) Having count(distinct convert(char(12), performance_dt) + matinee_or_evening) = 2)
If you do a separate line for each number of single concerts in your extraction, starting with 5 (you should change “= 2” into “>= 5” for the first list), you should get what you want.
Lucie
___________________________________
Lucie Spieler
IT Development and Training Manager
Editor, Season Program
Florida Grand Opera
8390 NW 25th Street
Miami, FL 33122
305.854.1643 x 1521
Box Office: 800.741.1010
To Give a Gift: 305.403.3315
www.FGO.org
2010-2011 | 70TH SEASON | THE MYSTERIES OF LOVE
Turandot | The Tales of Hoffmann | Don Giovanni | Cyrano
CORRECTION:
Try this instead:
AND Exists (select customer_no from vs_tck_hist (NOLOCK) Where vs_tck_hist.customer_no = a.customer_no and price_type = 'I' and season in (46) group by customer_no Having count(distinct convert(char(12), performance_dt) + matinee_or_evening) = 2)