Waitlist- AKA Zone history?

Off the top of my head, I'm guessing you'll need access to SSMS. Specifically, data from T_ORDER_SEAT_HIST, unless the system issued a ticket number. If it issued a ticket number, then you would have additional options.

Another idea is using TX_PERF_SEAT data. It has order_no, customer_no, perf_no, zone_no, zmap_no, seat_status, and prior_seat_status.

Hi Neil and Heath,

I looked up an order that had been moved from the waitlist zone to the registrant zone and pulled up the contents of t_order_seat_hist and TX_perf_seat for that order.  Unfortunately neither of those tables show the zone history.  Tickets are not issued for classes.

Any ideas for how I could find a table that has zone history?  I think Heath posted something on finding tables recently and I'll try to find that.  Appreciate your help.

This is the order in T_order_seat_hist

and here is the same order in TX_perf_seat

give this little number a go if should be enough to get you started (obs edit in your own perf_no etc - that was just my playing)

Oh, and for all the Table Look up stuff - it's in the Developer Wiki 

use impresario

Select --*
	a.id
	,a.event_date
	,a.customer_no
	,a.order_no
	,seat_row + ' ' +seat_num
	,d.description as event
	,c.description as zone

from T_ORDER_SEAT_HIST a
JOIN TX_PERF_SEAT b with (nolock)
	on a.seat_no = b.seat_no and a.perf_no = b.perf_no
JOIN V_ZONE_LIST c with (nolock)
	on b.zone_no = c.zone_no
Join TR_EVENT_CODE d
	on a.event_code = d.id
where a.perf_no = 1102
and a.seat_no = 13326
order by event_date

Thanks for the script, Heath! I manipulated it to help make this visualization in Excel.

I think it'll help justify our level of staffing in our Box Office during our fall evening events. We charged a fee to put the returned amount onto a gift certificate.

Heath, you never know who you'll inspire. Keep the posts coming.

Neil

I took Heath's query and made this version. You could change it to only look at your order_no to confirm it would work for you. You'll want to figure out the zone_no of your waitlist zone.

/*
Counts orders on all Returned seats within multiple production seasons.

Posted by Heath Wilder on Tessitura Forum

Made a few changes to only look for retuned tickets
nc
*/

Select --*
a.id
/*Use id to count how many seats/tickets*/
--,a.event_date
,a.order_no
--distinct(a.order_no) as [Order_no]
/*Use distinct order_no to only count # of orders*/
,a.customer_no
,cdn.display_name
,convert(date,a.event_date) [Return_date]
--,a.customer_no
--,seat_row + ' ' +seat_num as Row_Seat
,d.description as event
,d.id as [Event_id]
--,c.description as zone_with_Facility
, z.description as [ZONE]
, b.zone_no
--,c.zone_no
, i.description [Perf_Name]
, convert(date,p.perf_dt) [Perf_Date]
from T_ORDER_SEAT_HIST a
JOIN TX_PERF_SEAT b with (nolock)
on a.seat_no = b.seat_no and a.perf_no = b.perf_no
--JOIN V_ZONE_LIST c with (nolock)
-- on b.zone_no = c.zone_no
join T_ZONE z with (nolock) on b.zone_no = z.zone_no
Join TR_EVENT_CODE d
on a.event_code = d.id
join T_INVENTORY i on i.inv_no = a.perf_no
join T_PERF p on p.perf_no = a.perf_no
join FT_CONSTITUENT_DISPLAY_NAME() cdn on cdn.customer_no = a.customer_no
where a.perf_no in
(
select p2.perf_no from T_PERF p2 where p2.prod_season_no in (45147,45633,45201)
)
--and a.seat_no = 207727
--and c.zone_no = 278
and d.id = 3
--3 = Returned
order by Return_date, a.order_no
;

I took Heath's query and made this version. You could change it to only look at your order_no to confirm it would work for you. You'll want to figure out the zone_no of your waitlist zone.

/*
Counts orders on all Returned seats within multiple production seasons.

Posted by Heath Wilder on Tessitura Forum

Made a few changes to only look for retuned tickets
nc
*/

Select --*
a.id
/*Use id to count how many seats/tickets*/
--,a.event_date
,a.order_no
--distinct(a.order_no) as [Order_no]
/*Use distinct order_no to only count # of orders*/
,a.customer_no
,cdn.display_name
,convert(date,a.event_date) [Return_date]
--,a.customer_no
--,seat_row + ' ' +seat_num as Row_Seat
,d.description as event
,d.id as [Event_id]
--,c.description as zone_with_Facility
, z.description as [ZONE]
, b.zone_no
--,c.zone_no
, i.description [Perf_Name]
, convert(date,p.perf_dt) [Perf_Date]
from T_ORDER_SEAT_HIST a
JOIN TX_PERF_SEAT b with (nolock)
on a.seat_no = b.seat_no and a.perf_no = b.perf_no
--JOIN V_ZONE_LIST c with (nolock)
-- on b.zone_no = c.zone_no
join T_ZONE z with (nolock) on b.zone_no = z.zone_no
Join TR_EVENT_CODE d
on a.event_code = d.id
join T_INVENTORY i on i.inv_no = a.perf_no
join T_PERF p on p.perf_no = a.perf_no
join FT_CONSTITUENT_DISPLAY_NAME() cdn on cdn.customer_no = a.customer_no
where a.perf_no in
(
select p2.perf_no from T_PERF p2 where p2.prod_season_no in (45147,45633,45201)
)
--and a.seat_no = 207727
--and c.zone_no = 278
and d.id = 3
--3 = Returned
order by Return_date, a.order_no
;