Order Details Output Set

Emily Gildea $organization over 7 years ago

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

Lucie Spieler $organization over 7 years ago

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

Reply