• Replies 7 replies
  • Subscribers 702 subscribers
  • Views 142 views
  • Users 0 members are here
Options
Related

I need help with creating list

Amber Sanders
$organization

I need assistance on pulling the following lists for our Ticket Office.  I am at a lost on how to pull the lists. Can someone help me write sql query to create the the list in Tessitura?

  1. Any MC subscribers that have 2 or more orders of the same subscription series for this current season (FY23). 
  2. Any MC subscribers that have an initiator for their FY23 MC Subscription order. (I have no idea how to pull the initiator criteria)
  3. Any FY23 MC Subscribers that have been merged since 1/1/22 (I can figure out the accounts that were merged but not the date they merged) 

Any help is appreciated.  

Thank you, 

Amber Sanders

Parents
  • $organization

    For #2, something like this might work:

    select
	distinct o.customer_no
from T_SUB_LINEITEM as sli
	inner join T_PKG as pkg on pkg.pkg_no = sli.pkg_no
		and pkg.pkg_no <> 0
		and pkg.pkg_type in (1,2,3,4,5,6,7,8,9,10) --replace with the correct package types
	inner join TR_SEASON as s on s.id = pkg.season
		and s.fyear = 2023
	inner join T_ORDER as o on o.order_no = sli.order_no
		and o.initiator_no is not null

    Now, you are going to be getting the customer on the order (the "subscriber"), not the initiator.  This just says that there is a specified initiator.  if you wanted the actual initiator in your list you'd change the select to "distinct o.initiator_no".

Reply
  • $organization

    For #2, something like this might work:

    select
	distinct o.customer_no
from T_SUB_LINEITEM as sli
	inner join T_PKG as pkg on pkg.pkg_no = sli.pkg_no
		and pkg.pkg_no <> 0
		and pkg.pkg_type in (1,2,3,4,5,6,7,8,9,10) --replace with the correct package types
	inner join TR_SEASON as s on s.id = pkg.season
		and s.fyear = 2023
	inner join T_ORDER as o on o.order_no = sli.order_no
		and o.initiator_no is not null

    Now, you are going to be getting the customer on the order (the "subscriber"), not the initiator.  This just says that there is a specified initiator.  if you wanted the actual initiator in your list you'd change the select to "distinct o.initiator_no".

Children