Re: [Web-erp-developers] Need some help with MySQL query

SourceForge Headquarters 225 Broadway Suite 1600 San Diego, CA 92101 +1 (858) 422-6466

On Fri, 2004-03-26 at 01:24, Phil Daintree wrote:
> Hi Danie,
> 
> This SQL is only part of the story ... what about the quantity on sales orders 
> that cannot be delivered because of insufficient stock - we would wish to 
> purchase stock to cover these sales too?
> 
> I have kept all SQL simple with no subqueries etc to avoid cross SQL server 
> issues like this. I would do this in 3 round trips.
> 
> One for the demand - which would need to take into account the demand for 
> parent assemblies as well.
> One for the qty on order and another for the location stock quantites and 
> reorder level using PHP to calculate the quantity to buy.
Thanks, you have just highlighted that I have not as yet considered every thing,
the one thing that covers the report for now, is that it is just a
suggestion, however once again it should be reported that these late
delivery problems should also be highlighted.

I need to rethink this, with everything involved, including assemblies
and whether the products are manufactured etc. So I will come back with
something better.

Thanks

> I am confident that there is an answer to how the query could be constructed 
> in one bite ...but I'm too simple for that and it is possible that the 
> overhead associated with 3 round trips could be less than the LEFT RIGHT 
> INSIDE AND OUTSIDE JOINS necessary to acheive this in one SQL script. Also, 
> its a bitch to read monster SQL and understand the logic of scripts when the 
> SQL is too tough.
> 
> Take a look at StockStatus.php for how I did this.
I will, thanks. I was using stockValuation as a template.

Kind Regards
Danie Brink br...@na...

> 
> 
> Phil
> 
> 
> On Thursday 25 March 2004 21:25, Danie Brink wrote:
> > I have finalized the second query to this, and it works with some
> > casting under postgres, however it seems MySQL can not handle such a
> > query this way. What would be the correct way of doing this in MySQL?
> >
> > SELECT PurchOrders.IntoStockLocation, PurchOrderDetails.ItemCode,
> > Sum(PurchOrderDetails.QuantityOrd -
> > PurchOrderDetails.QuantityRecd) as Order
> > FROM PurchOrders, PurchOrderDetails, StockMaster
> > WHERE PurchOrders.OrderNo = PurchOrderDetails.OrderNo
> > AND PurchOrderDetails.Completed = 0
> > AND StockMaster.StockID = PurchOrderDetails.ItemCode
> > AND ( PurchOrders.IntoStockLocation, PurchOrderDetails.ItemCode ) IN  (
> > SELECT LocStock.LocCode, LocStock.StockID
> > FROM LocStock, StockMaster
> > WHERE LocStock.Quantity <= LocStock.ReorderLevel
> > AND LocStock.StockID=StockMaster.StockID )
> > GROUP BY PurchOrders.IntoStockLocation, PurchOrderDetails.ItemCode
> > ORDER BY PurchOrders.IntoStockLocation, PurchOrderDetails.ItemCode
> >
> > Below is the final queries I have decided on, it will limit the amount
> > of work that needs to be performed by the PHP script
> >
> > ////////////////////////////////////
> > //
> > // FINAL QUERIES FOR REORDER REPORT
> > //
> > ////////////////////////////////////
> >
> > // To Reorder - produce a list of stock that might to be reorders based
> > on stock catagory and location
> > SQL1  = "SELECT LocStock.LocCode, LocStock.StockID, LocStock.Quantity,
> > LocStock.ReorderLevel ";
> > SQL1 .= "FROM LocStock, StockMaster ";
> > SQL1 .= "WHERE LocStock.Quantity <= LocStock.ReorderLevel ";
> > SQL1 .= "AND
> > LocStock.StockID::character(20)=StockMaster.StockID::character(20) ";
> > SQL1 .= "AND StockMaster.CategoryID >= '" . $_POST['FromCriteria'] . "'
> > ";
> > SQL1 .= "AND StockMaster.CategoryID <= '" . $_POST['ToCriteria'] . "' ";
> > SQL1 .= "AND LocStock.LocCode = '" . $_POST['Location'] . "' ";
> > SQL1 .= "ORDER BY LocStock.LocCode, LocStock.StockID ";
> >
> > // Already Ordered - proude a list of stock that is already ordered and
> > in the list of possible reorders
> > SQL2  = "SELECT PurchOrders.IntoStockLocation,
> > PurchOrderDetails.ItemCode, ";
> > SQL2 .= "Sum(PurchOrderDetails.QuantityOrd -
> > PurchOrderDetails.QuantityRecd) as Order ";
> > SQL2 .= "FROM PurchOrders, PurchOrderDetails, StockMaster ";
> > SQL2 .= "WHERE PurchOrders.OrderNo = PurchOrderDetails.OrderNo ";
> > SQL2 .= "AND PurchOrderDetails.Completed = 0 ";
> > SQL2 .= "AND StockMaster.StockID = PurchOrderDetails.ItemCode ";
> > SQL2 .= "AND StockMaster.CategoryID >= '" . $_POST['FromCriteria'] . "'
> > ";
> > SQL2 .= "AND StockMaster.CategoryID <= '" . $_POST['ToCriteria'] . "' ";
> > SQL2 .= "AND PurchOrders.IntoStockLocation = '" . $_POST['Location'] .
> > "' ";
> > SQL2 .= "AND ( PurchOrders.IntoStockLocation, PurchOrderDetails.ItemCode
> > ) IN  (";
> > SQL2 .= "SELECT LocStock.LocCode, LocStock.StockID ";
> > SQL2 .= "FROM LocStock, StockMaster ";
> > SQL2 .= "WHERE LocStock.Quantity <= LocStock.ReorderLevel ";
> > SQL2 .= "AND LocStock.StockID=StockMaster.StockID ";
> > SQL2 .= "AND StockMaster.CategoryID >= '" . $_POST['FromCriteria'] . "'
> > ";
> > SQL2 .= "AND StockMaster.CategoryID <= '" . $_POST['ToCriteria'] . "' ";
> > SQL2 .= "AND LocStock.LocCode = '" . $_POST['Location'] . "'";
> > SQL2 .= ") ";
> > SQL2 .= "GROUP BY PurchOrders.IntoStockLocation,
> > PurchOrderDetails.ItemCode";
> > SQL2 .= "ORDER BY PurchOrders.IntoStockLocation,
> > PurchOrderDetails.ItemCode";
> >
> > ////////////////////////////////////
> >
> >
> >
> >
> > -------------------------------------------------------
> > This SF.Net email is sponsored by: IBM Linux Tutorials
> > Free Linux tutorial presented by Daniel Robbins, President and CEO of
> > GenToo technologies. Learn everything from fundamentals to system
> > administration.http://ads.osdn.com/?ad_id=1470&alloc_id=3638&op=click
> > _______________________________________________
> > Web-erp-developers mailing list
> > Web...@li...
> > https://lists.sourceforge.net/lists/listinfo/web-erp-developers
> 
> 
> 
> -------------------------------------------------------
> This SF.Net email is sponsored by: IBM Linux Tutorials
> Free Linux tutorial presented by Daniel Robbins, President and CEO of
> GenToo technologies. Learn everything from fundamentals to system
> administration.http://ads.osdn.com/?ad_id=1470&alloc_id=3638&op=click
> _______________________________________________
> Web-erp-developers mailing list
> Web...@li...
> https://lists.sourceforge.net/lists/listinfo/web-erp-developers
> 

Re: [Web-erp-developers] Need some help with MySQL query

Accounting & Best Practice Business Administration System

Re: [Web-erp-developers] Need some help with MySQL query