VbzCart/queries/qryOrdLines open

Details

• Requires:

1. REDIRECT Template:l/vc/query, qryPkgLines_byOrdLine_andItem

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

SQL

<mysql>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;</mysql>

old version

Details

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

SQL

<mysql>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;</mysql>