We (non ITS staff) are trying to pull a list in list builder to give us constituents who have a ticket order in a given date range which was paid with a particular payment type. We built the list with the following criteria:
ticket order date between X and YPayment type in Z
The SQL code reads
Select Distinct a.customer_no From V_CUSTOMER_WITH_PRIMARY_GROUP a (Nolock) JOIN LV_SMM_MAX_CONTRIBUTION e (Nolock) ON e.customer_no = a.customer_noJOIN lvs_tkt_hist f (Nolock) ON f.customer_no = a.customer_noJOIN t_payment g (Nolock) ON g.customer_no = a.customer_no Where IsNull(a.inactive, 1) = 1 AND e.cont_amt = 99.00 AND f.order_dt between '2012/01/01' And '2012/09/20 23:59:59' AND g.pmt_method in (54,53,69)
Trouble is, upon spot checking, it seems to be including customers who have an order in the right date range and who have a SEPARATE order with the appropriate payment type.
We're at a loss - any thoughts on how we can get this to join correctly to require the date and payment type both be on the same order? We looked at the descriptions for HAS and IN operators and it seemed that the IN operator should be correct, but obviously we are still missing something.
Thanks!
Hi Beth - Try using 'HAS' anyway - It seems like that might be actually what you want. If you use IN it means that anywhere in a customer's record they have an order in your date range and anywhere in their record they have that payment type. If you use HAS it will tie your payment type parameter to the orders in your date range.
e.g. the order in your date range must HAVE that payment type.
I think that might work...
Beth,
As a rule of thumb, if two elements are in different "groups", then they are being evaluated separately: And you can see that in the query, as your ticket history and your payment information are both being joined to the table based on customer number only.
Which is not the answer you want to hear or a solution to your problem, I'm afraid.
To get that information you would likely need a custom query or a custom listbuilder group built out of a new view that connects payments to ticket sales.
--Gawain
Gawain is correct. If you want to find a single order that was made in the date range you specified and paid by a particular payment method then both those pieces of data need to be in the same table or view (and you need to use IN for the operator on the Payment Method).
If this is something you are going to want to do a lot, you may want to look into adding payment method to your ticket history (which would require IT assistance to update the ticket history table, procedure, and screen).
Kevin Sheehan
Senior Documentation & Learning Resources Specialist
Tessitura Network
+1 888 643 5778 x 329
ksheehan@tessituranetwork.com
And one of my co-workers just pointed out to me that adding payment method to your ticket history may not be practical because you can have multiple payment methods used in an order, which I had forgotten.
You are probably going to need to get IT assistance here to write a query for you that makes the right joins between orders and payments to get you the results you want. You’ll need to join either your ticket history table (if it includes order #) or T_ORDER to T_TRANSACTION on the order # and then join T_TRANSACTION to T_PAYMENT in order to link payments to orders. I’m not very proficient with writing SQL queries, so I’m not going to attempt to be more specific than that, but that is the general direction you want to go.
Also keep in mind, that because of the possible multip-payments issue, the above query is only going to give you an order where a particular payment method was used, not necessarily one where it was the only payment method used or the payment method was used to pay off a specific order.
From: "Kevin Sheehan" <bounce-kevinsheehan4372@tessituranetwork.com>To: bvarro@smm.orgSent: Friday, September 28, 2012 12:34:58 PMSubject: RE: [Tessitura Technical Forum] List builder/operator problemAnd one of my co-workers just pointed out to me that adding payment method to your ticket history may not be practical because you can have multiple payment methods used in an order, which I had forgotten. You are probably going to need to get IT assistance here to write a query for you that makes the right joins between orders and payments to get you the results you want. You’ll need to join either your ticket history table (if it includes order #) or T_ORDER to T_TRANSACTION on the order # and then join T_TRANSACTION to T_PAYMENT in order to link payments to orders. I’m not very proficient with writing SQL queries, so I’m not going to attempt to be more specific than that, but that is the general direction you want to go. Also keep in mind, that because of the possible multip-payments issue, the above query is only going to give you an order where a particular payment method was used, not necessarily one where it was the only payment method used or the payment method was used to pay off a specific order. Kevin SheehanSenior Documentation & Learning Resources SpecialistTessitura Network+1 888 643 5778 x 329ksheehan@tessituranetwork.com 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!
Payment Details for Refunds is a standard report in the Finance folder which is meant for transacting refunds but can also be used for searching payments--just be careful to choose "No" for the parameter "Update Selected Rows?".
This report accepts criteria input for Payment date range, Payment amount range and Payment type. However it does not filter by performance or return any information about the order. It simply finds those payments made with that type for that amount on that day.
Jared
There is the Payments by Performance report, but that report doesn’t give you constituents, just payment totals.
From: "Kevin Sheehan" <bounce-kevinsheehan4372@tessituranetwork.com>To: bvarro@smm.orgSent: Friday, September 28, 2012 4:14:57 PMSubject: RE: [Tessitura Technical Forum] List builder/operator problemThere is the Payments by Performance report, but that report doesn’t give you constituents, just payment totals. Kevin SheehanSenior Documentation & Learning Resources SpecialistTessitura Network+1 888 643 5778 x 329ksheehan@tessituranetwork.com 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!