Difference between revisions of "VbzCart/queries/qryPkgLines qtys done"

Latest revision as of 15:46, 1 June 2011

Requires: ord_pkgs
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

<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>

@@ Line 5: / Line 5: @@
 * '''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,