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.
Thank You!! :-)