List Criteria - Paid Tickets versus Reserved Tickets

Jesse Dillman  Good point - I guess I would be looking for both - right now it only returns Owner, which is would be the Elementary School and in most cases does not have an email attached.  The Initiator returns the Teacher, which does have an email. 

I can't use the add affiliates/associates option, since that returns any and all teachers at Elementary School instead of just the one associated with that order.

There are also a handful of non-school associated orders, which would return a real person as the Owner instead of an Organization.

John Trimble  Yes, so my Paid list works great (using vs_ticket_history) and the email in Wordfly generates the list every day at 8am, so it's very unlikely I will miss someone who did pay (since most of these orders are coming in as checks through the mail and being manually input between 9am-5pm).  I was mostly trying to take the line I thought was pulling in Initiators from the Paid list criteria and adding it in to the Unpaid manual criteria Jesse had posted a few weeks ago - but my SQL skills are basically non-existent and I'm too new to Tessitura to know if t_ticket_history can even look at that - or if it does but it's called something else - etc.

Well the original query I sent you could be repurposed to pull that data in. There is probably a more elegant way to do it, but essentially what is happening in this statement is we are running the original query to pull in the "Owner" of the order, then we are combining it (hence the UNION statement) with a nearly identical query that looks at the same orders, but selects the initiator for the order if there is one present. Again replace the <title_no> with the ID # from your system. Do keep in mind that if the initiator is a primary affiliate for a household and you have replace primary affiliates with household selected, they will get removed or swapped out. And also try to keep in mind where you normally keep email addresses assigned for household accounts as well. Test it out with some output sets first and see if it yields the necessary data for your email list.

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)
UNION
SELECT o.initiator_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)
AND initiator_no is not null

Once again, amazing and did what I needed it to do.  Thank you so much!

Once again, amazing and did what I needed it to do.  Thank you so much!