Help with some SQL

Nicki LeGrand,

Given the above, I assume you are looking for the GL accounts for the actual PAYMENTS that were processed and not the GLs that were attached to the ticketing and fee layers attached to the price types on your performances.  If I have misunderstood, let me know.  But, assuming you want the GLs attached to the payments that your box office/website took, what you are going to want to do is something like this (below).  The problem with just linking the batch to the posting history table is that you definitely WILL get every single GL that was in that batch returned for every customer.  When it comes to payments, the best way is really just to go to the payment table to get started.  Then, when you finally link to the posting history table, you need to confirm that you are linking BOTH the post number AND the asset GL number from the associated payment method that was taken.

SELECT DISTINCT	p.customer_no,
				c.fname,
				c.lname,
				o.order_no,
				o.batch_no,
				o.created_by,
				o.create_dt,
				o.tot_ticket_paid_amt,
				o.tot_fee_paid_amt,
				o.transaction_no,
				gp.post_no,
				gp.gl_act_no,
				g.gl_description
FROM		T_PAYMENT p WITH (NOLOCK)
	JOIN	TR_PAYMENT_METHOD pm WITH (NOLOCK) ON p.pmt_method = pm.id
	JOIN	T_BATCH b WITH (NOLOCK) ON p.batch_no = b.batch_no
	JOIN	T_GL_POSTING_HISTORY gp WITH (NOLOCK) ON b.post_no = gp.post_no AND pm.asset_gl_no = gp.gl_act_no
	JOIN	T_CUSTOMER c WITH (NOLOCK) ON p.customer_no = c.customer_no
	JOIN	T_ORDER o WITH (NOLOCK) ON c.customer_no = o.customer_no AND b.batch_no = o.batch_no
	JOIN	T_GL_ACCOUNT g WITH (NOLOCK) ON gp.gl_act_no = g.gl_account_no
WHERE	p.batch_no = 35117
	AND	g.gl_description NOT IN ('Visa','Master Card','E Transfer Payments School')
ORDER BY	p.customer_no

Let me know if that does not help you get where you need to go.

John A. Moskal II

For completeness, and because someone said the word "audit", I'm going to have to bring up the eldritch horror of T_GL_ACCOUNT_HIST.  A lot of Tessitura reports use it: the purpose is to say "when the transaction occurred, this was the GL associated with the object in question." And allows you to generate reports that match past reporting.  If you need that kind of precision, you'll need to incorporate this table in.

For completeness, and because someone said the word "audit", I'm going to have to bring up the eldritch horror of T_GL_ACCOUNT_HIST.  A lot of Tessitura reports use it: the purpose is to say "when the transaction occurred, this was the GL associated with the object in question." And allows you to generate reports that match past reporting.  If you need that kind of precision, you'll need to incorporate this table in.