+- webERP Forum (http://www.weberp.org/forum)
+-- Forum: webERP Discussion (http://www.weberp.org/forum/forumdisplay.php?fid=1)
+--- Forum: How To ? (http://www.weberp.org/forum/forumdisplay.php?fid=6)
+--- Thread: Help with SQL (/showthread.php?tid=353)
Help with SQL - PakRicard - 09-12-2012
Hi all:
I'm trying to get in a single SQL:
stockid
description,
(materialcost + labourcost + overheadcost) as standard cost
from stockmaster
and
last price paid,
supplierid,
currency of the supplier,
effectivefrom date
from purchdata (but only the very last information from purchdata)
for all stockid with purchdata
I'm getting a too complicated SQL that gets out of memory problems.
is there a simpler way to do it?
Thanks!!!
RE: Help with SQL - PakRicard - 09-14-2012
I will try to explain in a different way.
I'm trying to build a SELECT returning me one row per stockid, containingdata :
from stockmaster (stockid, description and costs),
from purchdata ( the latest purchasing data available for that item, including supplierno, price and effective fromdate)
from suppliers (the currency of the supplier of the latest purchdata avilable for that item).
This SELECT should return all items with purchdata available.
The idea is to get an SQL to compare purchasing prices and standard costs and catch possible errors (miscalculations) in standardcost.
The only SQL I'm able to build return me many purchdata rows, and I'm not able to select the data for the newest one.
Thanks!!!
RE: Help with SQL - PakRicard - 09-14-2012
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