Querying Unsold Seats

Hi Sarah,

I've been doing something similar to monitor remaining inventory some big ticket releases we've had in the last few months. I've got a query that uses tx_perf_seat and counts when seat status = 0. I'm outputting totals based on date, but that table also includes the row and seat information you'd need to get location instead.

Hope that helps,

Scott

Here's some code you can start with.  It applies to just one particular performance id (perf_no).

--find seats of a certain status for a given perf_no
SELECT ps.perf_no
, ps.seat_status
, ss.description as seat_status_desc
, z.description as zone
, se.description as section2
, s.section
, s.seat_row as seat_row
, s.seat_num as seat_num
, ps.seat_no
FROM tx_perf_seat ps
JOIN tr_seat_status ss on ps.seat_status = ss.id
JOIN t_zone z on ps.zone_no = z.zone_no
JOIN t_seat s on ps.seat_no = s.seat_no
JOIN tr_section se on s.section = se.id
WHERE 1 = 1
AND ps.seat_status = 0 --0 is Available
AND ps.perf_no = 3950 --Your Perf no goes here
ORDER BY z.description, se.description, s.seat_row, convert(int, s.seat_num)

If you're going to do analysis you will need more than just available seat locations I would think. You will probably want to consider killed, held,and the constant house aggregates too. If so, this will help group by perf in a prod season. It is the engine I use in reports. Note the variable in the where clause and double check you ids for the seat statuses. 

select g.prod_season_no, i.description as prod_desc, a.perf_no, g.perf_dt,
sum(case
when a.seat_status in(1,4) then 1 -- 0 = Available; 1 = Held Breakable; 4 = Held
else 0
end
) as 'held',
sum(case
when a.seat_status in(0,18) then 1 -- 0 = Available; 1 = Held Breakable; 4 = Held
else 0
end
) as 'available',
SUM(case
when ISNULL(e.hc_no,0) = 120 then 0 --K holds Kills are not counted toward the Constant House
when b.is_seat = 1 then 1
else 0
end
) as 'constanthouse',
SUM(case
when isnull(e.hc_no,0) = 120 then 1
else 0
end
) as 'killed'
from TX_PERF_SEAT a
join T_SEAT b on a.seat_no = b.seat_no
join TR_SEAT_STATUS c on a.seat_status = c.id
left join TX_PERF_HC d on a.perf_no = d.perf_no and a.seat_no = d.seat_no and d.priority = 1
left join T_HC e on d.hc_no = e.hc_no
join T_PERF g on a.perf_no = g.perf_no
join T_INVENTORY i on g.prod_season_no = i.inv_no
where b.is_seat = 1
and g.prod_season_no = @prod_season
group by g.prod_season_no, i.description, a.perf_no, g.perf_dt

Scott, thank you so much for your response. This was very helpful! 

Hi Doug, thank you so much for this help!