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!
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!
There is the Payments by Performance report, but that report doesn’t give you constituents, just payment totals.
Kevin Sheehan
Senior Documentation & Learning Resources Specialist
Tessitura Network
+1 888 643 5778 x 329
ksheehan@tessituranetwork.com