Actual Attendance at performance in a List

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.

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

Thank You!! :-)

Thanks!