• Replies 2 replies
  • Subscribers 962 subscribers
  • Views 230 views
  • Users 0 members are here
Related

Order Details Output Set

Emily Gildea
$organization

Hello,

I am trying to develop an output set that will give individual order details, including fee amounts. Aside from constituent name and contact info, I have been able to pull the following order data filtered by order date:

order_dt, order_no, transaction_no, perf_dt, perf_name, num_seats, tot_ticket_paid_amt, tot_fee_paid_amt

I'd like to add payment method to this as well, but I'm stumped as to how to write the query. The query I am using for most of the data is:

(SELECT a.customer_no, a.order_no, a.order_dt, a.tot_ticket_paid_amt, a.transaction_no, a.tot_fee_paid_amt, b.perf_name, b.perf_dt, b.tck_amt, b.num_seats FROM t_order a JOIN vs_ticket_history b ON a.order_no = b.order_no) with a WHERE clause - order_dt between <<p1>> and <<p2>>

Any advice or suggestions would be much appreciated and if a report already exists for this, I would love to hear about it. I would use the Order Export Utility, but I need fee information, which isn't included on that.

Thanks!

Emily

  • $organization

    Try this on for size.  I joined your order select with T_Transaction to T_Payment to get the payment method. 

    SELECT

    a.customer_no,
    a.order_no,
    a.order_dt,
    a.tot_ticket_paid_amt,
    a.transaction_no,
    a.tot_fee_paid_amt,
    b.perf_name,
    b.perf_dt,
    b.tck_amt,
    b.num_seats,
    pmt.pmt_desc

    FROM t_order a
    JOIN vs_ticket_history b
    on a.order_no = b.order_no
    JOIN (Select
    distinct pm.description as pmt_desc,
    t.order_no as odr
    from T_TRANSACTION t
    Join T_PAYMENT p
    on t.sequence_no = p.sequence_no
    Join TR_PAYMENT_METHOD pm
    on p.pmt_method = pm.id) pmt on a.order_no = pmt.odr

    WHERE clause - order_dt between <<p1>> and <<p2>>

  • $organization
    Hi, Emily:
     
    You can join payment method to order by joining T_TRANSACTION to T_ORDER on the order_no, then T_PAYMENT to T_TRANSACTION on the transaction_no.
     
    SELECT distinct a.customer_no, a.order_no, a.order_dt, a.tot_ticket_paid_amt, a.transaction_no, a.tot_fee_paid_amt, b.perf_name, b.perf_dt, b.tck_amt, b.num_seats, e.description
    FROM t_order a
    JOIN vs_ticket_history b ON a.order_no = b.order_no
    join t_transaction c on c.order_no = a.order_no
    join t_payment d on d.transaction_no = c.transaction_no
    join TR_PAYMENT_METHOD e on e.id = d.pmt_method
    WHERE a.order_dt between '8/1/2018' and '8/2/2018'
     
    For one day of not-particularly busy sales, I show 21 rows for 5 orders, using your query, and 30 rows for 5 orders once I include payment method—and that’s using “distinct.” (Without the “distinct” I get 626 rows of data.) We are in subscription sales just now, which accounts for some of the multiple rows per order—and any contributions added another payment method (monies placed on account for later processing). So maybe you’d want to limit the payment methods in your Where clause to cash/check/credit card/gift certificate—or any payment method that is not an on account payment method, unless some of your orders are paid for using on account funds.
     
    Lucie
     

    Lucie Spieler
    IT Dev. & Training Manager
    Direct: 305.403.3291 | Box Office: 800.741.1010
    lspieler@fgo.org | www.FGO.org

    FLORIDA GRAND OPERA | 78th SEASON
    8390 NW 25th Street | Miami, FL 33122