AP Check History w/Freight

[jake29]

I posted this in the SQL forum but is more appropriate for this forum.

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

 

[stravis]

Change the following line:

inner join apDisFil_sql d2 ...

to:

left join apDisFil_sql d2 ...

Scott Travis

http://www.infospring.net

 

[jake29]

Thanks Scott,

I ended up with the following, I just have not had time to post it.

select apDisFil_sql.dist_dt,
	sum(apDisFil_sql.dist_amt) as total,
	apDisFil_sql.vchr_or_chk_no,
	apDisFil_sql.mn_no,
	apOpnHst_sql.chk_no,
	apVenFil_sql.vend_name,
	(select sum(dist_amt) from apDisFil_sql d2  where mn_no = '71700000' and d2.vchr_or_chk_no = apdisfil_sql.vchr_or_chk_no) as freight,
	chk_dt,
	apply_to_no,
	apOpnHst_sql.chk_no,
	apOpnHst_sql.vend_no

from apDisFil_sql   
	left join apOpnHst_sql on apDisFil_sql.vchr_or_chk_no = apOpnHst_sql.chk_vchr_no
	inner join apVenFil_sql  on apOpnHst_sql.vend_no = apVenFil_sql.vend_no
	
where apDisFil_sql.mn_no = '70100000' 
	
	
group by apDisFil_sql.vchr_or_chk_no,
	apOpnHst_sql.chk_no,
	apDisFil_sql.mn_no,
	apDisFil_sql.dist_dt,
	chk_vchr_no,
	apVenFil_sql.vend_name,
	chk_dt,
	apply_to_no,
	apOpnHst_sql.chk_no,
	apOpnHst_sql.vend_no
	

order by apDisFil_sql.vchr_or_chk_no

I would be interested in a comments you have on it, though.

Jake

 

[stravis]

It looks fine to me. There are many ways to solve any problem and no particular solution is better than another in SQL except when it requires significant system resources and time to run. If you are satisfied with the speed and the results then I think your solution is fine.

Scott Travis

http://www.infoSpring.net

 

[jake29]

Always interested in do something better, you have my attention.