Has anyone build a T_Keyword entry, for a list criteria for actual attendance to a specific Performance.
Did you do it with existing list criteria or did you create a custom view? If you have solved this one would you be willing to share a bit of SQL or a T-Keyword row that you use to set this up that would be very helpful.
How do you deal with the situation where a person buys 8 tickets and 4 had attended and 4 did not?
I need this too! We are currently running the Attendance by Performance report and saving as a list when we need this in an extraction.
We did this with a custom view that was (lovingly) ripped off from RP_ATTENDANCE_BY_PERFORMANCE procedure.
The code for our view is:
SELECT a.perf_no, b.customer_no, purchased = Count(*), attended = SUM(CASE WHEN ps.seat_status = 13 AND ps.available_ind = 0 AND (x.ret_sli_no <> a.sli_no or x.seat_no is NULL) THEN 1 ELSE 0 END), FROM [dbo].T_SUB_LINEITEM a With (NOLOCK) JOIN [dbo].T_ORDER b With (NOLOCK) on a.order_no = b.order_no LEFT OUTER JOIN (Select distinct perf_no, seat_no, ret_sli_no From [dbo].T_BENEVOLENT_SEAT b (NOLOCK)) x on a.seat_no = x.seat_no and a.perf_no = x.perf_no JOIN dbo.TX_PERF_SEAT ps with (NOLOCK) on a.perf_no = ps.perf_no and a.seat_no = ps.seat_no WHERE a.sli_status in (3,12,13) -- (seated/paid, ticketed, benevolent) GROUP BY a.perf_no, b.customer_no ORDER BY a.perf_no, b.customer_no
And then two criteria in T_KEYWORD to select the number of tickets that attended and the appropriate perf_no. You probably could come up with a percentage based calculation, but for our needs, we usually just want to know if there was any more than one ticket used.
Hello,
I added a column for number of seats attended to our local ticket history table and view [ LT_TKT_HIST and LVS_TKT_HIST ].
This way I can continue to query the same view that I was already using to check performance participation.
I also added the column to the ticket history screen so any user can see this number in the constituent record.
In List Manager I can now identify those patrons who had tickets for last night's performance, but none of their tickets were scanned. I have not configured a keyword to make a comparison between the two columns so I can pick out those patrons who had some of their tickets scanned but not all of their tickets. That would be interesting, I just haven't had a business case for it yet.
Jared
Like Paul, we've been using a custom view totally ripped off from the Tessi standard SP. But I love Jared's 'one step further' in adding it to the ticket history. Off to go do that!
And we don't worry about partial attendance for most of our correspondence. If any of the party came we consider them 'attended' when sending post show emails and the like.
- Heather
Saving the Attendance by Performance report to a list is a pretty good solution, as the parameters give you a lot of options for refining who you get (such as full attendance or partial attendance). But if you wanted to set up criteria to look at attendance, you could do it using the T_ORDER_SEAT_HIST table, which is where seat events are recorded each time a seat is marked as attended.
Event_Code is the column you’ll want to look at to identify seats that have been marked attended. Event codes are stored in TR_EVENT_CODE, which you can look at in System Tables. So I would probably set up a few new criteria, one for event code (with a dropdown populated with the event code for Attended), one for perf_no (you could set the dropdown up to display the performance names instead of IDs), and maybe price type if you wanted some extra filtering options. Then you could find everyone who had at least one ticket recorded as attended for a performance or everyone who has no tickets recorded as attended for a performance. You wouldn’t be able to get at people who used some but not all of their tickets without a manual edit to the code in your criteria set.
Please keep in mind that I haven’t tested any of this, so there could be some pieces of the puzzle I didn’t think about. Also keep in mind that when you add criteria that looks at a different table or view than the rest of your ticket criteria, you generally shouldn’t mix and match criteria from the two different sets and you generally won’t get the desired results. Finally, there might be some better ways to do this in v12 as we are adding the ability to add sub-entity criteria, which allow you to reference data that does not have a constituent ID on it if you can tie it back to a
parent entity that does have a constituent ID, which would allow you to use the seat history table instead and mix and match with your performance history criteria. We may even add a standard criteria element for attendance, though I can’t promise that.
Kevin Sheehan
Senior Technical Writer & Consultant
Tessitura Network
+1 888 643 5778 x 329
ksheehan@tessituranetwork.com
Thank You!! :-)
Thanks!