VbzCart/docs/queries/qryOrdLines open: Difference between revisions

From Woozle Writes Code
< VbzCart‎ | docs‎ | queries
Jump to navigation Jump to search
imported>Woozle
(New page: ==Details== * '''Requires''': {{vbzcart/query|qryOrders_Active}}, {{vbzcart/query|qryPkgLines_byOrdLine_andItem}} * '''Used by''': * '''History''': ** '''2008-11-02''' +WhenNeeded, +WhenSt...)
 
m (Woozle moved page VbzCart/VbzCart/queries/qryOrdLines open to VbzCart/docs/queries/qryOrdLines open without leaving a redirect: part 2)
 
(No difference)

Latest revision as of 01:55, 25 February 2024

Details

SQL

<section begin=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> <section end=sql />

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>

Retrieved from "https://wooz.dev/mw/index.php?title=VbzCart/docs/queries/qryOrdLines_open&oldid=6778"