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