VbzCart/docs/queries/qryPkgLines qtys done: Difference between revisions
Jump to navigation
Jump to search
imported>Woozle (fixed math for QtyDone; taking out grouping to speed things up) |
m (Woozle moved page VbzCart/VbzCart/queries/qryPkgLines qtys done to VbzCart/docs/queries/qryPkgLines qtys done without leaving a redirect: part 2) |
||
| (One intermediate revision by one other user not shown) | |||
| Line 5: | Line 5: | ||
* '''History''': | * '''History''': | ||
** '''2011-05-31''' created for packing slips | ** '''2011-05-31''' created for packing slips | ||
** '''2011-06-01''' added p.Seq so code can filter for packages ''before'' the current one -- so that the results will be the same (at least in theory) even after other packages have been created | |||
==SQL== | ==SQL== | ||
<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryPkgLines_qtys_done AS | <section begin=sql /><mysql>CREATE OR REPLACE VIEW qryPkgLines_qtys_done AS | ||
SELECT | SELECT | ||
p.Seq, | |||
p.ID_Order, | p.ID_Order, | ||
pl.ID_OrdLine, | pl.ID_OrdLine, | ||
Latest revision as of 01:55, 25 February 2024
About
- Requires: Template:Vbzcart
- Returns: "done" quantity for every line of all packages which have been checked in each order
- In order to return a current quantity status for each item in an order, this query should be filtered for that order ID (ID_Order) and then summed in code; summing in SQL and then filtering doesn't speed things up. (There's probably a way to do this in MySQL using advanced syntax I haven't dug into yet.)
- History:
- 2011-05-31 created for packing slips
- 2011-06-01 added p.Seq so code can filter for packages before the current one -- so that the results will be the same (at least in theory) even after other packages have been created
SQL
<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryPkgLines_qtys_done AS SELECT
p.Seq, p.ID_Order, pl.ID_OrdLine, pl.ID_Item, (IFNULL(pl.QtyShipped,0) + IFNULL(pl.QtyKilled,0) + IFNULL(pl.QtyNotAvail,0)) AS QtyDone
FROM
ord_pkgs AS p LEFT JOIN ord_pkg_lines as pl ON pl.ID_Pkg=p.ID
WHERE (WhenChecked IS NOT NULL);</mysql> <section end=sql />