Difference between revisions of "VbzCart/queries/qryPkgLines qtys done"
Jump to navigation
Jump to search
(Created page with "==About== * '''Requires''': {{vbzcart|table|ord_pkgs}} * '''Returns''': sum of "done" quantities for all packages which have been checked in each order ** In other words, you get...") |
(fixed math for QtyDone; taking out grouping to speed things up) |
||
Line 1: | Line 1: | ||
==About== | ==About== | ||
* '''Requires''': {{vbzcart|table|ord_pkgs}} | * '''Requires''': {{vbzcart|table|ord_pkgs}} | ||
− | * '''Returns''': | + | * '''Returns''': "done" quantity for every line of all packages which have been checked in each order |
− | ** In | + | ** 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''': | * '''History''': | ||
** '''2011-05-31''' created for packing slips | ** '''2011-05-31''' created for packing slips | ||
Line 12: | Line 11: | ||
pl.ID_OrdLine, | pl.ID_OrdLine, | ||
pl.ID_Item, | pl.ID_Item, | ||
− | + | (IFNULL(pl.QtyShipped,0) + IFNULL(pl.QtyKilled,0) + IFNULL(pl.QtyNotAvail,0)) AS QtyDone | |
FROM | FROM | ||
ord_pkgs AS p LEFT JOIN | ord_pkgs AS p LEFT JOIN | ||
ord_pkg_lines as pl ON pl.ID_Pkg=p.ID | ord_pkg_lines as pl ON pl.ID_Pkg=p.ID | ||
− | WHERE (WhenChecked IS NOT NULL) | + | WHERE (WhenChecked IS NOT NULL);</mysql> |
− | |||
<section end=sql /> | <section end=sql /> |
Revision as of 01:58, 1 June 2011
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
SQL
<mysql>CREATE OR REPLACE VIEW qryPkgLines_qtys_done AS SELECT
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>