• Replies 5 replies
  • Subscribers 415 subscribers
  • Views 89 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

Parents
  • $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. 

Reply Children
No Data