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

Hi John!

Thanks for this… I tried to run it and it returned a big fat nothing. And there are definitely payments on that batch. Any ideas why?

Thanks!!

The only big difference I see is that John is joining cutomer_no on the payment and not the order.  You might try shuffling those joins around?

It's something to do with specifying which Batch I want... if i take that out if returns everything, and if I query the T_PAYMENTS table alone with 35117 as the batch, it returns it..... tried specifying a different batch and that's also not returning anything. So strange. 

For the record, reshuffling increases my result set by over 300%.

Ok, sorry, what should I reshuffle? 

Try swapping T_ORDER and T_CUSTOMER joins out for this:

JOIN T_ORDER o WITH (NOLOCK) ON b.batch_no = o.batch_no
JOIN T_CUSTOMER c WITH (NOLOCK) ON o.customer_no = c.customer_no

But also, this query will only work for orders, contributions won't show up.

(That is what I get for just trying to do it on the fly without actually testing it in my own database.)

Ok, I did this, and it's still returning nothing for me: 

What does

Select * from T_PAYMENT where batch_no = 35117';

look like?

What does

Select * from T_PAYMENT where batch_no = 35117';

look like?

Returns everything from that Batch as expected (names etc redacted:): 

Okay, I've made a mistake here, which is that I thought that payments were tied to orders, but they're not, the two are connected via transactions (and batches, as written by John above).  This should give you something:

select
p.batch_no,
p.payment_no,
pm.description as payment_method,
p.pmt_amt,
p.customer_no as payment_customer,
o.customer_no as order_customer,
o.order_no,
p.transaction_no,
p.sequence_no

from T_PAYMENT as p
inner join TR_PAYMENT_METHOD as pm on pm.id = p.pmt_method
inner join T_TRANSACTION as t on t.transaction_no = p.transaction_no
and t.sequence_no = p.sequence_no
left outer join T_ORDER as o on o.order_no = t.order_no
where t.batch_no = 35117
order by
p.payment_no
;

Ok, yes, that definitely returned what I'm looking for!! Thank you!!!!! 

One more question.... how would you add the GL's to that? As in the GL's that the payments were made towards? 

That's where it starts to depend.  For us I would typically just add "pm.asset_gl_no" to the end of the select list.

However, if there is concern that at the time of transaction the gls might have been different, and you need that historical value, then we have to dig a little deeper.  T_GL_POSTING_HISTORY is kind of a horrible table in that it doesn't store or connect to the transaction/payment activities that we are using to screen on (for payment method type), and I believe it just has totals for the posting, so we would have to turn to T_GL_ACCOUNT_HIST.  Getting that to translate the payment methods and the posting date is tricky, I'd want to dig into some Tessitura stored procedures to figure out how they do it.

I'm definitely closer here then I was before. I'm going to go to my accounting manager and see if this is what she needs and can work with. Thank you SO MUCH for your help!