• Replies 8 replies
  • Subscribers 336 subscribers
  • Views 120 views
  • Users 0 members are here
Related

SQL Help - Payment Method

Former Member
Former Member $organization

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

Parents
  • $organization

    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!

Reply
  • $organization

    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!

Children
No Data