• Replies 19 replies
  • Subscribers 700 subscribers
  • Views 196 views
  • Users 0 members are here
Options
Related

SQL check?

Lesley Chaney
$organization

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) revenue
FROM T_TICKET_HISTORY TH
JOIN TR_PRICE_TYPE PT ON th.price_type = pt.id
WHERE th.season = 280
AND 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

Parents
  • $organization

    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.

  • $organization in reply to Neil Cole

    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!

  • $organization in reply to Lesley Chaney

    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.fyear
    FROM T_SUB_LINEITEM SLI
    JOIN T_ORDER O ON SLI.order_no = O.order_no
    LEFT 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_no
    JOIN TR_SEASON S ON S.id = p.season
    WHERE 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 Mode
    AND CONVERT(DATE,P.perf_dt) BETWEEN @Perf_Start_dt AND @Perf_End_dt
    GROUP BY S.fyear
    ORDER BY S.fyear
    ;
    */

  • $organization in reply to Neil Cole

    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.

  • $organization in reply to Lesley Chaney

    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?

Reply Children