webERP Forum
Need little help with a query - Printable Version

+- 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: Need little help with a query (/showthread.php?tid=8200)



Need little help with a query - HDeriauFF - 08-31-2018

Hi

I'm trying to resort my inventory by best sellers when printing a price list

I need to integrate to my selection the quantity of a stockid sold which is:

Code:
SUM(CASE WHEN stockmoves.type=10
OR stockmoves.type=11 THEN -qty ELSE 0 END)
as salesquantity



To the following query (used in PDFPriceList) :

Code:
"SELECT prices.typeabbrev,
prices.stockid,
stockmaster.description,
stockmaster.longdescription,
prices.currabrev,
prices.startdate,
prices.enddate,
prices.price,
stockmaster.materialcost+stockmaster.labourcost+stockmaster.overheadcost AS standardcost,
stockmaster.categoryid,
stockcategory.categorydescription,
prices.debtorno,
prices.branchcode,
custbranch.brname,
currencies.decimalplaces
FROM stockmaster INNER JOIN    stockcategory
ON stockmaster.categoryid=stockcategory.categoryid
INNER JOIN prices
ON stockmaster.stockid=prices.stockid
INNER JOIN currencies
ON prices.currabrev=currencies.currabrev
LEFT JOIN custbranch
ON prices.debtorno=custbranch.debtorno
AND prices.branchcode=custbranch.branchcode
WHERE prices.typeabbrev = '" . $SalesType . "'
AND stockmaster.categoryid IN ('". implode("','",$_POST['Categories'])."')
AND prices.debtorno='" . $_SESSION['CustomerID'] . "'
AND prices.startdate<='" . FormatDateForSQL($_POST['EffectiveDate']) . "'
AND (prices.enddate='0000-00-00' OR prices.enddate >'" . FormatDateForSQL($_POST['EffectiveDate']) . "')" .
$WhereCurrency . "
ORDER BY prices.currabrev,
stockcategory.categorydescription,
stockmaster.stockid,
prices.startdate"

So far, no luck putting a
Code:
INNER JOIN stockmoves ON stockmaster.stockid=stockmoves.stockid
next to the other JOINs, the selection only returns one record :-(

Thank you for your input!