Summing payments by campaign and payment method

Season Overview only looks at Ticket Sales whereas Transactions will also include Contributions and other non performance related transactions.

If you add 

and t.perf_no>0

You should remove all non-performance related transactions

Hope that helps

Mark

That's a thought, Mark, but that should largely be covered by our ticketing and contributions being handled by different seasons.  Some percentage of our contributions come in via ticketing orders: not sure how they would be categorized, but they come in "on account" and are then transferred to proper contributions.

I went ahead and tried it, and it drops the totals, but I'm still about 10% higher than the Season Overview numbers.

Also, won't this filter out fees?  Does Season Overview not include fees in its totals?

Thanks,

Gawain

That's a thought, Mark, but that should largely be covered by our ticketing and contributions being handled by different seasons.  Some percentage of our contributions come in via ticketing orders: not sure how they would be categorized, but they come in "on account" and are then transferred to proper contributions.

I went ahead and tried it, and it drops the totals, but I'm still about 10% higher than the Season Overview numbers.

Also, won't this filter out fees?  Does Season Overview not include fees in its totals?

Thanks,

Gawain

It filters out non-performance related items.

As far as I am aware so does Season Overview.
Thought it only counted the different price layers and that the Other Income column is essentially Price Layers that aren't Ticket income(or equivalent).

Took a look at my own data and ran it grouped by perf_no and compared it to 

select perf_no,
        pt_subs_amt+pt_single_amt+pt_discount_amt+pt_other_amt
         +pt_subs_contribution+pt_single_contribution+pt_discount_contribution+pt_other_contribution
from t_imp_perf_data

And was getting all sort of differences - when I looked at on performanec and ran the folowing queries

select SUM(t.trn_amt)
from T_TRANSACTION as t
where t.perf_no=14143

This returned the same results as Season Overview

select SUM(p.pmt_amt)
from T_TRANSACTION as t
inner join T_PAYMENT as p on t.transaction_no = p.transaction_no
            and t.sequence_no = p.sequence_no
where t.perf_no=14143

This didn't  in my case gave a lower value but it was due to transaction Type 31 Ticket Change which is a payment less exchange of tickets  ie no equivalent payment lines.

Which means money can be moved from one perf (and equivalent campaign) to another with no record in t_payment and so your query misses out on those transactions

The following query should sum all the payment less transactions by capaign

select t.campaign_no,
            SUM(t.trn_amt)
from T_TRANSACTION as t
left join T_PAYMENT as p on t.transaction_no = p.transaction_no
            and t.sequence_no = p.sequence_no
where p.sequence_no is null
group by t.campaign_no

See if this accounts for your differences

Mark

Ah, I guess I had concluded that every payment had 1+ transactions, but had incorrectly assumed that every transaction mapped to a payment, so that's very good to know.

Unfortunately, those excess transactions account for something like 0.25% of the variance.

Alternatively, as I dig through I think I'm seeing that other transactions (on account transfers, fees) are starting to be in the ball park of the difference.