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
You may also want to look at price layers depending on if your organization builds fees within the ticket price.
thank you!
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.
No, the Ticket History Role - Owner/Initiator/Recipient
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).