List Criteria - Paid Tickets versus Reserved Tickets

I don't think there are any out of the box criteria that look at unpaid orders. For lists, you can manually edit the criteria if you have a SQL query you want to use. This would probably work, but you have to lookup to see what the ID# is for your School Day Performance title. To find that just open Ticketing Setup and it should take you to Title Setup screen. Just find the ID number that corresponds to this title.

Once you have discerned what the ID # for that title is, you need to replace <title_no> with that number:
SELECT o.customer_no
FROM T_ORDER o join T_SUB_LINEITEM s on o.order_no = s.order_no
join T_PERF p on s.perf_no = p.perf_no
join T_PROD_SEASON ps on p.prod_season_no = ps.prod_season_no
join T_PRODUCTION pr on ps.prod_no = pr.prod_no
WHERE s.sli_status in (1,2)
AND pr.title_no = <title_no>
AND p.perf_dt <= DateAdd(dd, 7, CONVERT (VARCHAR, GetDate(), 112) + ' 23:59:59.997')
               AND p.perf_dt >= DateAdd(dd, 7, CONVERT (VARCHAR, GetDate(), 112))
AND not exists (SELECT * FROM T_TICKET_HISTORY WHERE customer_no = o.customer_no and perf_no = p.perf_no)

Keep in mind, that this query excludes partially paid orders. Those orders will have ticket history and will appear in the list you described above. If you want a separate list for partially paid orders, then that would be a whole different query.

Amazing, that did the trick.

Out of curiosity, why would partially paid orders be a different query?  My (completely uneducated in SQL) assumption in reading this is that the line about sli_status is pulling in the unpaid status - isn't there just a number that indicates partially paid you could add in there?  Or can you only do one sli_status at a time?

Amazing, that did the trick.

Out of curiosity, why would partially paid orders be a different query?  My (completely uneducated in SQL) assumption in reading this is that the line about sli_status is pulling in the unpaid status - isn't there just a number that indicates partially paid you could add in there?  Or can you only do one sli_status at a time?

Sub Line Items represent individual tickets, which can be either paid or unpaid, in this instance. There is no partial paid status. So a partially paid order will have some of the sub lineitems with the status of 1 or 2 for unpaid, and others with a status of 3 or 12 for paid. The paid ones will appear in the customer's ticket history and will pull into your first list even though they still have unpaid tickets in the order.

If you don't normally do partially paid orders, then it shouldn't be a problem, or if you are fine with partially paid orders appearing in your list for paid customers, then these 2 lists should work.

Good point with the SLI status. Check out TR_SLI_STATUS (which is why Jesse had sli.sli_status in (1,2) in the query).

Thank you, that makes sense.

For extra fun, here's a bit of Tessitura esoterica about what order money gets allocated in a situation where a ticket order is only getting partially paid. From the very bottom of this page in the documentation: https://docs.tessitura.com/Help_System_v151h/Tessitura.htm#Ticket_Order_Processing/Payment%20Window.htm

I remember learning this ages ago and this thread reminded me of it again!

When payments are made for a ticket order, the money is applied to the order contents in the following sequence:

1. Contributions

2. Fees

3. Performances, in the following sequence:

   1. By sub line item status, first Seated, Unpaid (SUP), then Unseated, Unpaid (UNS)

   2. By performance date and time, from earliest to latest

   3. By sub line item number, from lowest to highest

   Performances are handled the same regardless of whether they are included in a package or not

That is actually some good knowledge to have. Especially the performance sequence. 

Circling back around why unpaid unseated orders are missing from out of the box criteria:  They look at the view vs_ticket_history that's populated overnight from t_ticket_history, which is populated more frequently as mentioned above from orders, sub_lineitems,etc, where, among other details, the sli_status is ticketed and paid. The levels of roiling complication in all the details point to Order Search to sort them all out at any given moment. 

Any idea on how to get the Ticket History Role into this?  I'm trying to pull in only the initiators. 

On my Paid List, I have a query that looks like this: 

SELECT DISTINCT a.customer_no
FROM V_CUSTOMER_WITH_PRIMARY_GROUP a WITH(NOLOCK)
JOIN(
SELECT a1.customer_no
FROM vs_ticket_history a1 WITH (NOLOCK)
WHERE a1.perf_dt >= DateAdd(dd, 7, CONVERT (varchar, GetDate(), 112))
AND a1.perf_dt <= DateAdd (dd, 7, CONVERT (varchar, GetDate (), 112) + ' 23:59:59.997')
AND a1.title_no IN (199)
AND a1.role IN (2, 3, 6, 7)) AS e ON e.customer_no = a.customer_no
WHERE a.inactive = 1

Which gets me exactly where I need to be for that list.  I can tell that line about a1.role IN is pulling in the Ticket Role History, but no matter how I modify my Unpaid List's query I can't seem to get it to work. 

 vs_ticket_history  is seated paid, so no unpaid.  Order search is not set and forget, but it looks (can look) at upaid without having to drill into SLIs. Also, vs_ticket_history updates overnight, so not even paid if it was paid today. 

So I guess it depends on what you mean by only initiators. The roles provided through ticket history account for customers who play at least 1 or more roles in the order. So for most orders that don't have the initiator, the owner is also the initiator. So I guess the question is, do you want a list of only customers indicated in the initiator field, or do you want initiators if there is one but otherwise you want the owner?