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
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.