Skip to Content
0
Former Member
Jan 11, 2011 at 12:26 PM

Not picking up credit notes

30 Views

This report is working apart from it is not picking up credit note quantities from inv1.quantity - how do I include credit note quantities in the inv1.quantity total?

SELECT t1.itemcode as 'Item Code', t2.frgnname as 'Status', t2.onhand as 'Stock', str(sum(t1.quantity),19,0) as 'Sales'

FROM oinv t0 inner join inv1 t1 on t0.docentry = t1.docentry inner join oitm t2 on t1.itemcode = t2.itemcode

WHERE

t0.docdate >= '[%0]' and

t0.docdate <= '[%1]' and

t2.frgnname = '[%2]' and

t1.itemcode > '01000' and

t1.itemcode < '99999' and

t2.onhand > '0'

GROUP BY t1.itemcode, t2.frgnname, t2.onhand

ORDER BY t1.itemcode