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