Hello,
Does anyone have a nefty way of calculating the zone capacity for a performance?
Naomi
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!