• Replies 5 replies
  • Subscribers 415 subscribers
  • Views 87 views
  • Users 0 members are here
Related

Pull Orders Processed to Individual where Household Exists

Michelle Blau (Past Member)
$organization

Hello all, 

We are about to launch our digital season and it has come to our attention that in order for patrons to view the productions, the order must be processed on the household level if one exists. I'm trying to figure out a way to pull all orders in this season that were processed to an individual where a household exists. Then, we would transfer the orders to the household.

I imagine a query would be possible, however, I'm still a SQL beginner. 

Unfortunately, time is of the essence. Any help would be appreciated! 

Thanks in advance, 

Michelle

  • $organization

    How many accounts are impacted? This can be done manually within a household record by selecting the household member and then in the menu bar going to Constituents > Household Operations > Move transactions to household. If you have a few hands on deck to help crank those out, it might be a faster project than going through the SQL route. 

  • $organization

    Is your transact_as_household_order setting in T_DEFAULTS set to "no"? Setting that to "yes" will make it so all ticket orders will be redirected to the household automatically for future orders. It marks the individual placing the order as the initiator on the order if you still need to know which individuals are placing orders.

  • $organization

    I think the SQL option would be something like this:

    Select o.customer_no , o.order_dt , o.order_no , c.fname , c.lname
    ,HouseHoldCustNo , HouseHold  ,tot_paid_amt , tot_ticket_return_amt
    , a.affiliation_type_id , aft.description as AffiliationType
     from T_order as O
    inner join t_customer as C on O.customer_no = c.customer_no
    inner join (Select * from  T_AFFILIATION where inactive = 'N') as A on o.customer_no = A.individual_customer_no
    inner join (Select customer_no as HouseHoldCustNo, lname as HouseHold from T_CUSTOMER where cust_type = 7) as H
        on a.group_customer_no = H.HouseHoldCustNo
    left join TR_AFFILIATION_TYPE as AFT on a.affiliation_type_id = aft.id
    where c.cust_type =1
    and exists (Select 1 from T_LINEITEM as L inner join T_PERF as P on l.perf_no = p.perf_no
    where l.order_no = o.order_no
    --and  P.season in (1,2,3,4,5)
    and perf_dt between '01/01/2020' and '12/31/2020'  
    )

    The results look like this:

  • $organization in reply to Ronald Radford

    Thank you! Our web developers were actually able to work it out, but this is definitely what I was looking for. 

  • $organization

    Thanks all! We got it sorted out with our web developers.