AP Check History

[jake29]

I am working on recreating the check history report in Crystal to show freight cost per voucher. Using the apDisFil and apOpnHst tables I am able to return what I need except for lines that do not have a freight charge. Once I get past this I will add the apVenFil back in to complete the report.

select d1.dist_dt,
d1.dist_amt,d2.dist_amt,
(d1.dist_amt + d2.dist_amt) as TT, 
d1.vchr_or_chk_no,
d1.mn_no,d2.mn_no,
op.chk_no,
op.pay_amt,
op.disc_taken

from apDisFil_sql d1 

left outer join apOpnHst_sql op on d1.vchr_or_chk_no = op.chk_vchr_no 
inner join apDisFil_sql d2 on d2.vchr_or_chk_no  =  d1.vchr_or_chk_no

where  d1.vend_no = '000000000157' 
and d1.mn_no =('30150000')  
and d2.mn_no = ('71700000') 
and op.chk_no = '23409'

order by d1.vchr_or_chk_no

Results

20050812	497.96	37.62	535.58	40788	30150000	71700000	23409	530.24	5.36

20050812	1609.82	137.94	1747.76	40789	30150000	71700000	23409	1730.33	17.48

20050812	3575.99	200.64	3776.63	40794	30150000	71700000	23409	3738.91	37.77

20050812	259.90	12.54	272.44	40795	30150000	71700000	23409	269.72	2.72

20050812	2429.92	175.56	2605.48	40796	30150000	71700000	23409	2579.48	26.06

20050816	35.99	26.20	62.19	40889	30150000	71700000	23409	61.57	.62

If anyone has done anything like this and could point me in the right direction it would be appreciated.

SQL 2000
Macola 7.6.100

Thanks,

Jake

 

[vongrunt]

More info please - in particular:

- purpose of each of three tables involved
- relationships between tables (1:m, m:1, blah...)
- info about relevant columns

Sample data from tables would also be nice.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[jake29]

As you have gathered there are actually 2 tables (apOpnHst, apDisFil) with the apDisFil d2 being aliased.

op apOpnHst
d1 apDisFil
d2 apDisFil

The tables are use to show how a Check is distributed to POs


apOpnHst for Check Number 23409 - Voucher 40796

vend_no	ap_opn_dt	ap_opn_tm	vchr_no	vchr_chk_cd	vchr_chk_type	apply_to_no	trx_dt	opn_clos_cd	sep_chk_fg	status	cash_mn_no	chk_no	seq_no	vchr_type	ap_PO_no	pay_amt	vchr_vchk_no	chk_dt	disc_taken	reference	ap_mn_no	orig_trx_rt	curr_trx_rt	jnl_cd	jnl_doc_no	username	id_no	doc_no	doc_dt	identity
157	20050825	113656	23409	X	5	1522766	20050812	O	N	R	10210000	23409	1	A	6849200	2579.48	40796	20050825	26.06	                                                   Ven 000000000157 Cck:00023409 	30100000	1	1	CP2026	40796	       JUDYB   000000001572005082511365600023409X5 23409	     20050825	195865

apDisFil for Voucher 40796

dist   mn_no	sb_no	vchr_or_chk_no	dist_dt	jnl_src	vend_no	dist_amt	int_fg	reference	orig_trx_no	curr_trx_no	Id_no	Job_no	User_name	Identity
2	30100000	0          40796	     20050812	AP1898 	157	      -2605.54	Y	Ven000000000157  Vch:00040796 	1	1	0000000001572005082209332600040796V1              	NULL	ROBINK    	223801
1	30150000	0          40796	     20050812	AP1898 	157	     2429.92	Y	Ven000000000157  Vch:00040796 	1	1	0000000001572005082209332600040796V1              	68492	ROBINK    	223802
1	70100000  1030000       40796	     20050812	AP1898 	157	    0.06	Y	Ven000000000157  Vch:00040796 	1	1	0000000001572005082209332600040796V1              	68492	ROBINK    	223803
6	70100000  1030000       40796	     20050812	AP1898 	157	   2429.92	Y	Ven000000000157  Vch:00040796 	1	1	0000000001572005082209332600040796V1              	68492	ROBINK    	223804
1	70140000	0          40796	      20050812	AP1898 	157	   -2429.92	Y	Ven000000000157  Vch:00040796 	1	1	0000000001572005082209332600040796V1              	68492	ROBINK    	223805
1	71700000 1030000        40796	      20050812	AP1898 	157	  175.56	Y	Ven000000000157  Vch:00040796 	1	1	0000000001572005082209332600040796V1              	68492	ROBINK    	223806

Let me know if this is clear??