https://htyp.org/mw/index.php?action=history&feed=atom&title=VbzCart%2Fqueries%2FqryOrdLines_openVbzCart/queries/qryOrdLines open - Revision history2026-07-01T12:49:03ZRevision history for this page on the wikiMediaWiki 1.45.3https://htyp.org/mw/index.php?title=VbzCart/queries/qryOrdLines_open&diff=10927&oldid=prevWoozle: New page: ==Details== * '''Requires''': {{vbzcart/query|qryOrders_Active}}, {{vbzcart/query|qryPkgLines_byOrdLine_andItem}} * '''Used by''': * '''History''': ** '''2008-11-02''' +WhenNeeded, +WhenSt...2008-12-03T23:45:41Z<p>New page: ==Details== * '''Requires''': {{vbzcart/query|qryOrders_Active}}, {{vbzcart/query|qryPkgLines_byOrdLine_andItem}} * '''Used by''': * '''History''': ** '''2008-11-02''' +WhenNeeded, +WhenSt...</p>
<p><b>New page</b></p><div>==Details==<br />
* '''Requires''': {{vbzcart/query|qryOrders_Active}}, {{vbzcart/query|qryPkgLines_byOrdLine_andItem}}<br />
* '''Used by''':<br />
* '''History''':<br />
** '''2008-11-02''' +WhenNeeded, +WhenStarted<br />
* '''Notes''': Much faster than old version -- about 7 seconds<br />
==SQL==<br />
<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryOrdLines_open AS<br />
SELECT<br />
ol.*,<br />
QtyDone,<br />
o.Number,<br />
WhenNeeded,<br />
WhenStarted<br />
FROM <br />
(ord_lines AS ol<br />
LEFT JOIN qryOrders_Active AS o<br />
ON ol.ID_Order=o.ID)<br />
LEFT JOIN qryPkgLines_byOrdLine_andItem AS pl<br />
ON pl.ID_OrdLine=ol.ID<br />
WHERE (o.ID IS NOT NULL) AND (IFNULL(QtyDone,0) <> QtyOrd)<br />
ORDER BY Number, ID_Item;</mysql><br />
<section end=sql /><br />
==old version==<br />
===Details===<br />
* '''Requires''': qryOrdLines_PkgdQtys<br />
* '''Notes''': Very slow -- about takes 40 seconds<br />
===SQL===<br />
<mysql>CREATE OR REPLACE VIEW qryOrdLines_open AS<br />
SELECT<br />
ol.ID,<br />
o.ID AS ID_Order,<br />
ol.ID_Item,<br />
Sum(ol.QtyOrd) AS QtyOrd,<br />
Sum(ol.QtyDone) AS QtyDone,<br />
Min(WhenOpened) AS WhenOldestOrder,<br />
Min(WhenNeeded) AS WhenSoonestDue<br />
FROM qryOrders_Active AS o LEFT JOIN qryOrdLines_PkgdQtys AS ol ON ol.ID_Order=o.ID<br />
WHERE IFNULL(ol.QtyOrd,0)-IFNULL(ol.QtyDone,0)<>0<br />
GROUP BY ol.ID, o.ID, ol.ID_Item;</mysql></div>Woozle