SQL Help - Payment Method

Hey Marty,

I’m finding that the results of that script are including fees for every perf in the order instead of just the specified perf. I figure I need to join the perf_no with something but a different table than is listed in the code now.  Got any ideas?

I will catch up with you at the conference!

Glad I could help,

YOU ARE MY HERO!!! Where do I ship the chocolate?

Thank you so much.

Gloria

How about this,

SELECT     

      p.pmt_method,

      f.description,

      pm.description,

      Sum(p.pmt_amt) AS amount

FROM

      T_PAYMENT p Join

      T_TRANSACTION t ON p.transaction_no = t.transaction_no AND p.sequence_no = t.sequence_no JOIN

      TR_TRANSACTION_TYPE tt ON tt.id = t.trn_type JOIN

      T_FEE f ON f.fee_no = t.fee_no JOIN

      TR_PAYMENT_METHOD pm ON pm.id = p.pmt_method

WHERE

      f.fee_no in (202,203,204) and

      t.transaction_no IN

            (SELECT    

            t.transaction_no

      FROM

            T_TRANSACTION t

      WHERE

            t.perf_no IN (6478))

GROUP BY

      p.pmt_method,

      f.description,

      pm.description

Thanks but no, that isn’t breaking out the fees for me.

Try this,

SELECT     

      pm.Description,

      Sum(p.pmt_amt) AS Amount

FROM

      T_PAYMENT p Join

      T_TRANSACTION t ON p.transaction_no = t.transaction_no AND p.sequence_no = t.sequence_no JOIN

      T_FEE f ON f.fee_no = t.fee_no JOIN

      TR_PAYMENT_METHOD pm ON pm.id = p.pmt_method

GROUP BY

      pm.description

Marty Jones

Director of Information Services

Omaha Performing Arts
1200 Douglas Street

Omaha, Nebraska 68102

P 402.661.8469

Marty.Jones@omahaperformingarts.org

www.omahaperformingarts.org

For tickets, call Ticket Omaha at 402.345.0606

From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Gloria Ormsby
Sent: Friday, April 08, 2011 12:19 PM
To: Martin A. Jones
Subject: [Tessitura Technical Forum] SQL Help - Payment Method

Hey SQL Gurus...

I am writing a query to find the total amount of fees paid by which payment method. My code is producing the wrong results in that it is lumping the payment into the wrong payment method. For example, the code below results in

10.19  Visa-Box
7.92 Mastercard-Box

The total is right but I have orders where fees were paid by a payment method not listed here. Can you please take a look at my code and see if anything jumps out at you?

SELECT 

SUM(ISNULL(sf.fee_amt_paid,0)), d.pmt_method, f.description, pm.description

FROM 

dbo.T_SLI_FEE sf

JOIN 

dbo.T_LINEITEM l ON sf.li_seq_no = l.li_seq_no

JOIN  t_fee f ON f.fee_no = sf.fee_no

JOIN 

t_order o ON o.order_no = l.order_no

AND 

o.order_no = sf.order_no

JOIN 

dbo.T_TRANSACTION t ON t.sequence_no = sf.li_seq_no

JOIN 

[dbo].t_payment d ON t.transaction_no = d.transaction_no and t.sequence_no = d.sequence_no

JOIN 

dbo.TR_PAYMENT_METHOD pm ON d.pmt_method = pm.id

WHERE 

sf.fee_no IN (202,203,204)

AND 

l.perf_no = 6478

--@perf_no

AND 

sf.fee_amt_paid > 0

GROUP 

BY d.pmt_method, f.description, pm.description

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!

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!

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!

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!

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!

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!

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!

Hey Marty,

I’m finding that the results of that script are including fees for every perf in the order instead of just the specified perf. I figure I need to join the perf_no with something but a different table than is listed in the code now.  Got any ideas?

I will catch up with you at the conference!

Glad I could help,

YOU ARE MY HERO!!! Where do I ship the chocolate?

Thank you so much.

Gloria

How about this,

SELECT     

      p.pmt_method,

      f.description,

      pm.description,

      Sum(p.pmt_amt) AS amount

FROM

      T_PAYMENT p Join

      T_TRANSACTION t ON p.transaction_no = t.transaction_no AND p.sequence_no = t.sequence_no JOIN

      TR_TRANSACTION_TYPE tt ON tt.id = t.trn_type JOIN

      T_FEE f ON f.fee_no = t.fee_no JOIN

      TR_PAYMENT_METHOD pm ON pm.id = p.pmt_method

WHERE

      f.fee_no in (202,203,204) and

      t.transaction_no IN

            (SELECT    

            t.transaction_no

      FROM

            T_TRANSACTION t

      WHERE

            t.perf_no IN (6478))

GROUP BY

      p.pmt_method,

      f.description,

      pm.description

Thanks but no, that isn’t breaking out the fees for me.

Try this,

SELECT     

      pm.Description,

      Sum(p.pmt_amt) AS Amount

FROM

      T_PAYMENT p Join

      T_TRANSACTION t ON p.transaction_no = t.transaction_no AND p.sequence_no = t.sequence_no JOIN

      T_FEE f ON f.fee_no = t.fee_no JOIN

      TR_PAYMENT_METHOD pm ON pm.id = p.pmt_method

GROUP BY

      pm.description

Marty Jones

Director of Information Services

Omaha Performing Arts
1200 Douglas Street

Omaha, Nebraska 68102

P 402.661.8469

Marty.Jones@omahaperformingarts.org

www.omahaperformingarts.org

For tickets, call Ticket Omaha at 402.345.0606

From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Gloria Ormsby
Sent: Friday, April 08, 2011 12:19 PM
To: Martin A. Jones
Subject: [Tessitura Technical Forum] SQL Help - Payment Method

Hey SQL Gurus...

I am writing a query to find the total amount of fees paid by which payment method. My code is producing the wrong results in that it is lumping the payment into the wrong payment method. For example, the code below results in

10.19  Visa-Box
7.92 Mastercard-Box

The total is right but I have orders where fees were paid by a payment method not listed here. Can you please take a look at my code and see if anything jumps out at you?

SELECT 

SUM(ISNULL(sf.fee_amt_paid,0)), d.pmt_method, f.description, pm.description

FROM 

dbo.T_SLI_FEE sf

JOIN 

dbo.T_LINEITEM l ON sf.li_seq_no = l.li_seq_no

JOIN  t_fee f ON f.fee_no = sf.fee_no

JOIN 

t_order o ON o.order_no = l.order_no

AND 

o.order_no = sf.order_no

JOIN 

dbo.T_TRANSACTION t ON t.sequence_no = sf.li_seq_no

JOIN 

[dbo].t_payment d ON t.transaction_no = d.transaction_no and t.sequence_no = d.sequence_no

JOIN 

dbo.TR_PAYMENT_METHOD pm ON d.pmt_method = pm.id

WHERE 

sf.fee_no IN (202,203,204)

AND 

l.perf_no = 6478

--@perf_no

AND 

sf.fee_amt_paid > 0

GROUP 

BY d.pmt_method, f.description, pm.description

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!

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!

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!

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!

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!

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!

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!