Summing payments by campaign and payment method

Hmmm.  I don't think that's going to quite do it: as I read that it's going to give me only campaigns and payment methods that have only had one payment on them.  Even just checking against transaction_no and sequence_no won't do it because you have have many rows of payments associated with a single transaction/sequence pair.  If I check for payment_no, transaction_no, sequence_no sets, there are no duplicates:

 

 

select
	p.payment_no,
	t.transaction_no,
	t.sequence_no,
	COUNT(*) as count
from
	T_PAYMENT as p
	inner join T_TRANSACTION as t on t.transaction_no = p.transaction_no
		and t.sequence_no = p.sequence_no
group by
	p.payment_no, t.transaction_no, t.sequence_no
order by
	COUNT(*) desc, t.transaction_no desc

 

 

Hi,
Please add one more condition
inner join T_TRANSACTION as t1 on t.sequence_no = p.sequence_no
in your query

Malek

select
 t.campaign_no,
 p.pmt_method,
 SUM(p.pmt_amt) as pmt_sum,count(*)
 from
 T_PAYMENT as p
 inner join T_TRANSACTION as t WITH (NOLOCK) on t.transaction_no = p.transaction_no
 and t.sequence_no = p.sequence_no
 group by
 t.campaign_no,
 p.pmt_method
 having count(*)=1
 order by t.campaign_no, p.pmt_method

Please try this

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

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.