RE: Help with SQL
Got it! Not very ellegant but effective. I think there must be a better way to do it, but...
SELECT stockmaster.stockid,
stockmaster.description,
purchdata.supplierno,
purchdata.conversionfactor,
purchdata.price,
suppliers.currcode,
purchdata.suppliersuom,
purchdata.effectivefrom,
stockmaster.lastcostupdate,
(stockmaster.materialcost + stockmaster.labourcost + stockmaster.overheadcost) AS stdcost,
stockmaster.units
FROM purchdata, stockmaster, suppliers
WHERE purchdata.stockid = stockmaster.stockid
AND stockmaster.discontinued = 0
AND suppliers.currcode = "USD"
AND purchdata.supplierno = suppliers.supplierid
AND purchdata.effectivefrom = (SELECT MAX(p2.effectivefrom)
FROM purchdata p2
WHERE p2.stockid = purchdata.stockid)
ORDER BY stockmaster.stockid
Regards,
Pak Ricard
|