VbzCart/queries/qryOrdLines open

Details

 * Requires: ,
 * Used by:
 * History:
 * 2008-11-02 +WhenNeeded, +WhenStarted
 * Notes: Much faster than old version -- about 7 seconds

SQL
CREATE OR REPLACE VIEW qryOrdLines_open AS SELECT ol.*, QtyDone, o.Number, WhenNeeded, WhenStarted FROM (ord_lines AS ol   LEFT JOIN qryOrders_Active AS o    ON ol.ID_Order=o.ID) LEFT JOIN qryPkgLines_byOrdLine_andItem AS pl ON pl.ID_OrdLine=ol.ID WHERE (o.ID IS NOT NULL) AND (IFNULL(QtyDone,0) <> QtyOrd) ORDER BY Number, ID_Item;

Details

 * Requires: qryOrdLines_PkgdQtys
 * Notes: Very slow -- about takes 40 seconds

SQL
CREATE OR REPLACE VIEW qryOrdLines_open AS SELECT ol.ID, o.ID AS ID_Order, ol.ID_Item, Sum(ol.QtyOrd) AS QtyOrd, Sum(ol.QtyDone) AS QtyDone, Min(WhenOpened) AS WhenOldestOrder, Min(WhenNeeded) AS WhenSoonestDue FROM qryOrders_Active AS o LEFT JOIN qryOrdLines_PkgdQtys AS ol ON ol.ID_Order=o.ID WHERE IFNULL(ol.QtyOrd,0)-IFNULL(ol.QtyDone,0)<>0 GROUP BY ol.ID, o.ID, ol.ID_Item;