SQL guru’s….help needed!!!
I am trying to create a report for our finance department that reports on Payment Method (specifically Amex, Visa and MC) by performance, contribution (by fund or campaign depending) and fee.
I have tried (and tried) and failed to come up with an accurate way of joining T_TRANSACTION (which seems to act as the bridge between T_PAYMENT and other tables like T_SUB_LINEITEM, T_CONTRIBUTION etc.) onto anything that means we can report on pmt_method via perf etc.
I saw a long forum post dated 2009 that spoke about issues joining these tables and wondered if there had been any solutions in the last couple of years? It seemed at that point, that T_TRANSACTION was a bridge view ONLY, and these 2 sets of tables were unable to be joined. Is this still the case?
Thanks for any advice,
Natasha
Natasha,
You should be able to join t_payment to t_transaction using sequence_no. The only tricky thing is you have to treat certain transaction types differently and exclude certain ones.For example you probably don't want to include transaction types like "Future to Current" and "Non-Restricted to Restricted"
Also, for Fee Change and Ticket Change transaction types, you will need to use trn_amt instead of pmt_amt since there is no payment involved with these transaction types.
For gifts you would probably want to do a different query with different joins but still joining t_payment to t_transaction using sequence_no.
Dale
Hi,
In response to Dale's comment, due to something I found out today...
I had a query on a show settlement report where the value of the seats purchased didn't equal the payments that were being applied to the show. After lots of head scratching I found it was down to the join between t_transaction and t_payment (using transaction_no and sequence_no). Basically what had happened was that a customer had returned their seats for the performance, and then bought another show. So while there were transaction in and out of the perf, there was still only 1 payment, and if you follow the join it would show on the first show...
Over the next few days I'm giong to be investigating how we can get more accurate report, and will post back with what I find...
That's exactly why you would want to exclude ticket change and fee change transactions from your primary query but include them in a secondary query pulling trn_amt from t_transaction rather than pmt_amt from t_payment.
dale
What a great response, thanks everyone for all your replies! It is somewhat comforting to know that this was not just something really simple I was missing.
Thanks Heather and Ryan for bringing up the Payments by Performance standard report, a quick glance over this and already I can see it’s going to be a great help. Now that I realize what finance is asking for specifically is not possible, I can work on giving them the info they need in a different format. I couldn’t see one, but there isn’t a report like this for contributions is there?
Dale thanks for you advice on totaling contributions too, I’m from a Box Office background so still not 100% confident with that area.
I look forward to your report Simon....