I have been asked to find the top 100 ticket buyers in our system. (Based totally on the number of ticket purchased.) But I'm not sure what is the best way to go about finding this information. I do have T-Stats but I'm stumped on the recipe. Any ideas?
Thanks,
Elizabeth Carlock
I don't have t-stats, but a general approximation can be found by running sql:
select top 100 o.customer_no, COUNT(o.customer_no) as tickettotal
from dbo.T_SUB_LINEITEM s
inner join T_ORDER o on s.order_no = o.order_no
group by o.customer_no
order by tickettotal desc
This just counts the subline items, which also include refunds comps etc..., but as its just a top 100, the answer will be approximately correct
The difficulty with using T-stats for something like this is that it looks at ranges, but not really individual constituents. As much as I love T-stats, I think you are better off with the sql from Tessitura.
From: Tessitura Ticketing Forum [mailto:forums-ticketing@tessituranetwork.com] On Behalf Of Wayne EvansSent: Friday, July 06, 2012 9:43 AMTo: Nicole KeatingSubject: Re: [Tessitura Ticketing Forum] Top 100 Ticket Buyers
From: Elizabeth Carlock <bounce-elizabethcarlock8778@tessituranetwork.com>Sent: 7/5/2012 2:33:25 PM
This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Ticketing Forum. You may reply to this message to post to the Ticketing 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!
Hi Elizabeth,
You could do this in T-Stats by using one of the Custom Constituent Summary elements to count non-comp tickets. Try updating one of the Custom_Const elements in LTRX_TS_ELEMENT_DIVISION to the following : (make sure to replace [production tessitura server] with your actual server instance, and if you use local ticket history tables change T_TCK_HIST to LT_TKT_HIST)
Data_select
RIGHT('00000'+CONVERT(varchar(30),SUM(num_seats)),5)
Data_from
[production tessitura server].impresario.dbo.T_TCK_HIST a
join [production tessitura server].impresario.dbo.lv_ts_season_division b on a.season=b.season
join [production tessitura server].impresario.dbo.tr_price_type c on a.price_type=c.id
Data_where
b.division=! and c.price_type_category <> 4
If you're on v11, the data_from should be more like:
[production tessitura server].impresario.dbo.V_CUSTOMER_WITH_PRIMARY_AFFILIATES q
join [production tessitura server].impresario.dbo.t_tck_hist a on a.customer_no=q.expanded_customer_no
join [production tessitura server].impresario.dbo.LV_TS_SEASON_DIVISION s on t.season=s.season
This change would be available after your next T-Stats refresh. You might then sort your report to see how many constituents had the highest number(s) of tickets. One thing to note, you may find more than 100 households may be tied at the top for number of purchased tickets. You could choose to slice and dice by some other criteria to determine who you want to include in your "Top 100" or you could choose to include the ties.