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?
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