SQL check?

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.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
;
*/

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
;
*/

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).