Hello,
Does anyone have a nefty way of calculating the zone capacity for a performance?
Naomi
The simple answer is that tx_perf_seat has one row for each seat in a performance, so finding how many seats per zone for a performance is straight forward.
However, every organization calculates capacity differently. Do you include blacked out seats? Are there standard management holds or tickets that you exclude, etc. etc.
David
Shared Report solution #120 (in the Reference Materials project on TASK) is the Zone Map Capacity Proof report. It takes a facility and a zone map as the parameters and returns a capacity count per zone.
As others have said this depends on your blackout seat kill setup. Below is one way to see counts by zone. Adjust for your own blackout/seat kill setup
declare @perf_no int
select @perf_no = 1062
select
a.perf_no,
production = i.description,
perf_code = a.perf_code,
a.perf_dt,
ps.zone_no,
z.rank,
z.abbrev,
capacity_seats = count(distinct ps.seat_no),
sum(count(distinct ps.seat_no)) Over (Partition by a.perf_no) as perf_cap
from tx_perf_seat ps (nolock)
join tr_seat_status ss (nolock) on ps.seat_status = ss.id
join t_perf a (nolock) on ps.perf_no = a.perf_no
left join tx_perf_hc hc (nolock) on ps.perf_no = hc.perf_no and ps.seat_no = hc.seat_no and hc.hc_no = 60 --boardkill holds
left join t_hc h (nolock) on hc.hc_no = h.hc_no
join t_zone z (NOLOCK) on ps.zone_no = z.zone_no
join t_inventory i (nolock) on a.prod_season_no = i.inv_no
where ss.status_code != 'BLK'
and isnull(h.hc_no,0) != 60
and a.perf_no = @perf_no
group by a.perf_no,
i.description,
a.perf_code,
z.abbrev
You all rock! Thank you so much!