limited SQL skills

Bingo! Found it thank you  so much!!

DARRELL JONES | Customer Service Coordinator | Tasmanian Symphony Orchestra Pty Ltd

1 Davey Street, Hobart, Tasmania | GPO Box 1450 Hobart, Tasmania                                        

Tel 03 6232 4404 | Email jonesd@tso.com.au | Website www.tso.com.au

From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Ken McSwain
Sent: Friday, 5 February 2016 2:04 PM
To: Darrell Jones
Subject: Re: [Tessitura Technical Forum] limited SQL skills

Hi Darrell

It's actually quite tricky, because the connection between payments and what they've paid for is not direct.

If you're on v12, something like the below should work, substituting your payment method and perf no's of course - if you're still on v11, the T_PERF_PRICE_TYPE table doesn't exist, and you'd have to do something similar with T_PERF_PMAP (I think)

-----------------------------------------------------------------

select p.customer_no  , p.pmt_amt, p.pmt_dt , pm.description payment_method, p2.perf_no, p2.perf_code, p2.perf_dt, t.order_no

FROM dbo.T_PAYMENT AS p

JOIN dbo.T_TRANSACTION AS t ON p.sequence_no = t.sequence_no

JOIN dbo.TR_PAYMENT_METHOD AS pm ON p.pmt_method = pm.id

JOIN dbo.T_PERF_PRICE_TYPE AS ppt ON ppt.id = t.pmap_no

JOIN dbo.T_PERF AS p2 ON ppt.perf_no =p2.perf_no

WHERE p.pmt_method = 24

AND p2.perf_no = 112379

Hello someone,

I am trying to locate a very small amount of money paid with an incorrect payment type. I've isolated the performance where it's been used but it doesn't relate to a price type

With my minute knowledge of SQL I am trying to write what I thought would be a simple query to identify the constituent file with the payment type and with the performance, with limited success.

Could someone help with the select "from" and "from" "where" and "where" please?

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!

Bingo! Found it thank you  so much!!

DARRELL JONES | Customer Service Coordinator | Tasmanian Symphony Orchestra Pty Ltd

1 Davey Street, Hobart, Tasmania | GPO Box 1450 Hobart, Tasmania                                        

Tel 03 6232 4404 | Email jonesd@tso.com.au | Website www.tso.com.au

From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Ken McSwain
Sent: Friday, 5 February 2016 2:04 PM
To: Darrell Jones
Subject: Re: [Tessitura Technical Forum] limited SQL skills

Hi Darrell

It's actually quite tricky, because the connection between payments and what they've paid for is not direct.

If you're on v12, something like the below should work, substituting your payment method and perf no's of course - if you're still on v11, the T_PERF_PRICE_TYPE table doesn't exist, and you'd have to do something similar with T_PERF_PMAP (I think)

-----------------------------------------------------------------

select p.customer_no  , p.pmt_amt, p.pmt_dt , pm.description payment_method, p2.perf_no, p2.perf_code, p2.perf_dt, t.order_no

FROM dbo.T_PAYMENT AS p

JOIN dbo.T_TRANSACTION AS t ON p.sequence_no = t.sequence_no

JOIN dbo.TR_PAYMENT_METHOD AS pm ON p.pmt_method = pm.id

JOIN dbo.T_PERF_PRICE_TYPE AS ppt ON ppt.id = t.pmap_no

JOIN dbo.T_PERF AS p2 ON ppt.perf_no =p2.perf_no

WHERE p.pmt_method = 24

AND p2.perf_no = 112379

Hello someone,

I am trying to locate a very small amount of money paid with an incorrect payment type. I've isolated the performance where it's been used but it doesn't relate to a price type

With my minute knowledge of SQL I am trying to write what I thought would be a simple query to identify the constituent file with the payment type and with the performance, with limited success.

Could someone help with the select "from" and "from" "where" and "where" please?

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!