| Receipt# |
Date |
Cost |
Balance |
Supplier |
Select |
select
osrv.code,
osrv.barcode,
osrv.odate,
osrv.rate as cost,
c_suppliers.description as supplier,
COALESCE(a.rqty,a.rqty,0)-(COALESCE(b.sqty,b.sqty,0)+COALESCE(c.dqty,c.dqty,0)) as balance
from osrv
left join c_suppliers on c_suppliers.code=osrv.client
left join (select barcode,coalesce (sum(osrv.qty),sum(osrv.qty),0)-coalesce(osrv.rqty,sum(osrv.rqty),0) as rqty from osrv group by barcode) as a on a.barcode=osrv.barcode
left join (select barcode ,coalesce(sum(osiv.qty),sum(osiv.qty),0)-coalesce(osiv.rqty,sum(osiv.rqty),0) as sqty from osiv group by barcode ) as b on b.barcode=osrv.barcode
left join (select barcode ,coalesce(sum(odelivery.qty),sum(odelivery.qty),0) as dqty from odelivery
where odelivery.status=0 group by barcode ) as c on c.barcode=osrv.barcode
where osrv.item=''
group by osrv.barcode
order by osrv.odate desc limit 20