Dear Community,
I'm wanting to return data from LT_TKT_HIST where payment method = x ....but pmt_method is not available in the table.
So I try to link LT_TKT_HIST to a table that has payment method... like T_PAYMENT by linking customer_no..but this does not seem to work and is returning everything regardless of payment type.
Can this be done? Help needed
---SQL Example:
SELECT DISTINCT
a.customer_no,a.perf_name,a.perf_dt,a.order_dt,a.tck_amt,a.num_seats,a.location,b.pmt_method
FROM LT_TKT_HIST a, T_PAYMENT b
WHERE
a.customer_no = b.customer_no ANDb.pmt_method = x
You need link t_transaction table to t_order table then go to customer_no
Other side, t_transaction table link to t_payment.
So between t_order table and t_payment table, it has to be the t_transaction table, in this way, sales path can connect to payment path at the order_no level.
Have fun.
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Aaron Waapu Sent: Thursday, 30 January 2014 5:34 PM To: Ben Gu Subject: Re: [Tessitura Technical Forum] SQL: LT_TKT_HIST vs Payment Method ID
Hey Ken,
My SQL skills have been dormant for quite some time so I best leave this one for the experts and see where it gets me. I wish it were as simple as including a payment method column in the table?
This little piece of work is only for a small group of constituents anyway.... probably no need for major changes at this point.
Thanks again,
From: Ken McSwain <bounce-kenmcswain5454@tessituranetwork.com> Sent: 1/30/2014 12:02:38 AM
Hi Aaron
That's somewhere between impossible and just impossibly difficult.
There's no explicit link in Tess between a specific ticket purchase and the payment method that paid for it, mostly because of the necessity to deal with the complex possibilities of multiple/partial payments, exchanges, and so on. The links are complex, and involve moving through tables composed of payment fragments, transaction fragments, and the item fragments in the T_SLI_DETAIL table, and trying to re-assemble them into wholes. And then you need to consider that if a ticket is exchanged, it loses any connection to the original payment method anyway. That just shifts the level of difficulty up another notch. And unless you've done some customisation of LT_TKT_HIST, there's nothing in there to do the join on.
Kevin's suggestion will get you slightly closer, but would only work if every customer had only ever made one purchase. If you're happy with an approximate result, you could possibly link on the order number, and derive the most likely payment method via the order, assuming there's only one payment on the order and selecting the first one that pops up, but it needs reasonably good SQL skills to attempt something like that. .
I wouldn't even try, unless you were desperate - and if you are desperate, I'd recommend talking to Tess and getting them to provide someone like Peter Nelson to try and help. He still carries scars from trying to do this sort of thing at the Opera House.
Ken
This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!
try this:
SELECT TOP (10) d.*, b.pmt_methodFROM T_SUB_LINEITEM AS d INNER JOIN T_ORDER AS c ON d.order_no = c.order_no INNER JOIN T_TRANSACTION AS a ON c.order_no = a.order_no INNER JOIN T_PAYMENT AS b ON a.transaction_no = b.transaction_noWHERE (b.pmt_method = 8)---change 8 to something else