• Replies 7 replies
  • Subscribers 336 subscribers
  • Views 60 views
  • Users 0 members are here
Related

Summing payments by campaign and payment method

Gawain Lavers
$organization

I'm trying to sum payment dollars by campaign and payment method, but my figures are enough off of those listed in Season Overview that I'm concerned that I'm counting some payments twice.

My query is simple and looks like this:

select
	t.campaign_no,
	p.pmt_method,
	SUM(p.pmt_amt) as pmt_sum
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
	t.campaign_no,
	p.pmt_method
order by t.campaign_no, p.pmt_method

 

I'm off (higher than Season Overview) by about $1,000,000: enough that I'm not convinced that returns or gift certificates or something else like that would be responsible.  Anyone have any ideas on what I'm missing?

 

 

 



[edited by: Gawain Lavers at 12:43 PM (GMT -6) on 29 Dec 2016]
Parents
  • $organization
    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

    On 12/29/2016 12:54 PM, Gawain Lavers wrote:

    I'm trying to sum payment dollars by campaign and payment method, but my figures are enough off of those listed in Season Overview that I'm concerned that I'm counting some payments twice.

    My query is simple and looks like this:

    select
 t.campaign_no,
 p.pmt_method,
 SUM(p.pmt_amt) as pmt_sum
 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
 t.campaign_no,
 p.pmt_method
 order by t.campaign_no, p.pmt_method

    I'm off (higher than Season Overview) by about $1,000,000: enough that I'm not convinced that returns or gift certificates or something else like that would be responsible.  Anyone have any ideas on what I'm missing?

     

     

     




    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!

  • $organization in reply to Anavarodin Malek

    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

     

     

Reply
  • $organization in reply to Anavarodin Malek

    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

     

     

Children
No Data