Hello smart friends.
I've been asked for the following information for the past 5 seasons:
Total single ticket sales (units sold, households, and revenue)
My first stab at getting the answers is this:
SELECT count (customer_no) cust_count, sum (num_seats) seat_qty, sum (tck_amt) revenueFROM T_TICKET_HISTORY THJOIN TR_PRICE_TYPE PT ON th.price_type = pt.idWHERE th.season = 280AND price_type_group IN (4,5)
And my result is this:
Any thoughts or advice on if I'm close to being able to answer this question? Anything else I should think about?
Thanks as always!
Lesley
I think you'll want to use "count (distinct customer_no)".
thank you Gawain!
You may want to restrict it by ticket history role, as well
What's that?
Ooh, thanks! I JUST thought of that too.
Owner, Initiator, etc
That's the one that trips us up here all the time, so I thought I'd mention it
Can you tell me if it's only looking for "currently seated" orders, or if this is also including voo, vto, etc?
Warning regarding using T_TICKET_HISTORY. It has ALL history of tickets. It does not subtract returned or refunded tickets. I'd suggest you start from T_SUB_LINEITEM and then go out from there. It will let you leave out any returned tickets.
I think we've all had our experience getting incorrect numbers out of T_TICKET_HISTORY. Welcome to the club. However, there are circumstances when you would use that table, but they are few.
This is all the kinds of "things we've learned the hard way" advice that I was looking for! I'll go investigate that.
Thanks Neil!