Post Reply 
 
Thread Rating:
  • 0 Votes - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Need little help with a query
08-31-2018, 07:18 AM (This post was last modified: 08-31-2018 09:33 AM by HDeriauFF.)
Post: #1
Need little help with a query
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!
Find all posts by this user
Quote this message in a reply
Post Reply 


Forum Jump:


User(s) browsing this thread: 1 Guest(s)