VbzCart/queries/qryPkgLines qtys done

from HTYP, the free directory anyone can edit if they can prove to me that they're not a spambot
Jump to navigation Jump to search
The printable version is no longer supported and may have rendering errors. Please update your browser bookmarks and please use the default browser print function instead.

About

  • 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

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>

Retrieved from "https://htyp.org/mw/index.php?title=VbzCart/queries/qryPkgLines_qtys_done&oldid=15392"