• Replies 2 replies
  • Subscribers 962 subscribers
  • Views 232 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

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

Reply
  • $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>>

Children
No Data