Hello hivemind,
Our marketing/dev team is looking to send an email (L2) to all patrons that still have on account $ from cancelled productions. We would like to display the dynamic amount each constituent has on account. However, there is no on account output set elements.
Additionally, I have only used the default output set in the past.
Any ideas?
Michelle
Michelle,
Below is what we use for On Account output:
TR_QUERY_ELEMENT_GROUP
Description: On Account
Data From: (Select customer_no, NULLIF((-1 * SUM(pmt_amt)),0) as 'amt' From [dbo].t_payment a JOIN [dbo].vrs_payment_method b WITH (NOLOCK) ON a.pmt_method = b.id Where b.pmt_type = 2 group by customer_No)
Data Where: left blank
TR_QUERY_ELEMENT
Description: On Account Balance
Group Id: On Account
Data Select: !.amt
Single Row: checked
Thank you! I will give this a try!