Hi All,
Good day.
I have a question about t_order_seat_hist.
I want to find out a seat is sold or not based on t_order_seat_hist table only.
Is this possible?
I tried to create a report to travel back time by passing in a certain date as parameter, then try to determine conditions of seat. but I got about 20% less from sales report.
My question is this:
If we have a sli_no, then we start checking t_order_seat_hist table, what kind of method should we use to figure out the seat is a sold record or not?
(let's say sli_status in(3,12) is a sold ticket.)
Thank you very much for your time.
Ben
Hi Ben
For seat quantities, I use the event_code column. Give the below a whirl (I think it was lifted from the Daily Sales Report and various guises of custom reports thereafter?). Be mindful of complimentary tickets if you are counting these as a separate entity, I think you can identify these with the sli_paid_amt in the same table (T_ORDER_SEAT_HIST).
Cheers, Ben
Normal 0 false false false EN-GB X-NONE X-NONE MicrosoftInternetExplorer4
DECLARE
@event_code_reserved int,
@event_code_returned int,
@event_code_released int,
@event_code_new_paid int,
@event_code_new_unpaid int,
@event_code_new_adjust int
SELECT
@event_code_reserved = 1,
@event_code_returned = 3,
@event_code_released = 4,
@event_code_new_adjust = 25,
@event_code_new_paid = 26,
@event_code_new_unpaid = 27
select
a.perf_no ,
sold_count = (SUM(CASE
WHEN a.event_code = @event_code_new_paid THEN 1
when a.event_code = @event_code_reserved and a.amount = 0 THEN 1
WHEN a.event_code = @event_code_new_unpaid THEN -1
WHEN a.event_code = @event_code_returned THEN -1
WHEN a.event_code = @event_code_released and a.amount = 0 THEN -1
END)
from t_order_seat_hist a (NOLOCK)
where a.event_date between @pmt_st_dt and @pmt_end_dt
GROUP by a.perf_no
Take a look at RP_TICKET_SALES_BY_PERIOD for the most recent version of the “count seats using T_ORDER_SEAT_HIST” SQL. Keep in mind that you should only look at seated counts (where seat_no > 0).
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Ben MagsonSent: Wednesday, October 13, 2010 9:00 AMTo: Ryan CrepsSubject: Re: [Tessitura Technical Forum] SOLD or NOT: a question about t_order_seat_hist table
From: Ben Gu <bounce-bengu4278@tessituranetwork.com>Sent: 10/12/2010 1:13:26 AM
This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!
As far as determining you difference between the baseline report and the history report, goes, I would recommend...
Determine which procedure is getting your baseline report. At some point in the code it is likely that you are going to individual sub lineitems. Get those sublineitems and throw them into a work table.
Get similar, non-aggregated data from your history table and throw that into a work table
Compare the two work tables to determine where the variance is coming from take it from there. I would avoid aggregating anything unless it's really necessary.
Thank you very much Mr.Magson .
this is what I am looking for.
have fun.
Hi Ryan,
Thank you very much.
RP_TICKET_SALES_BY_PERIOD give me a clear idea how I should do the counting.
Thank you for your time.
have fun
Hi Matthew,
Thank you very much for your help.
I totally agree with you. We should keep the detail table as long as it is needed.
That is why I always like to keep sli_no, detail_sli_no in the tmp tables.
Devil is in the detail. So when you keep the detail, you always can catch the devil.