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
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!
Your query could be something similar to this query (it is commented out by a /* */):
/*DECLARE @Perf_Start_dt DATE = '2019-07-01' DECLARE @Perf_End_dt DATE = '2021-06-30' --DECLARE @MinFY INT = 2017; SELECT COUNT(DISTINCT O.customer_no) AS [Cust_Count],SUM(SLI.paid_amt) AS [Paid_amt] ,COUNT(SLI.sli_no) AS [TICKETS SOLD] , s.fyearFROM T_SUB_LINEITEM SLI JOIN T_ORDER O ON SLI.order_no = O.order_noLEFT JOIN TR_PRICE_TYPE PT ON PT.id = SLI.price_type JOIN T_PERF P ON P.perf_no = SLI.perf_no JOIN T_INVENTORY I ON I.inv_no = SLI.perf_no AND I.inv_no = P.perf_noJOIN TR_SEASON S ON S.id = p.seasonWHERE P.season IN (170,192,253) AND SLI.sli_status IN (2,3,12) --2 = seated, unpaid --3 = seated, paid --12 = ticketed, paid --13 = Return in Benevolent ModeAND CONVERT(DATE,P.perf_dt) BETWEEN @Perf_Start_dt AND @Perf_End_dt GROUP BY S.fyearORDER BY S.fyear;*/
This is great Neal - thank you!
I do also need to filter by role, but any time I try to add it in, the query thinks WAY too hard and doesn't return anything.
Hey Lesley! In terms of filtering, are you just wanting to ensure that your customer counts only include the order owner, or are you wanting to get back separate counts (or something else) for the initiators and recipients?
STEVE MOORE!
That's exactly it - I'm only looking for the owners.
It's me! :-D You should be set then with something like above, the customer_no column in T_ORDER is the owner (initiator_no is separate, and recipient info is in T_SUB_LINEITEM).