VbzCart/docs/queries/qryPkgLines qtys done ord sum: Difference between revisions
Jump to navigation
Jump to search
imported>Woozle (Created page with "==About== * '''Requires''': {{vbzcart/query|qryPkgLines_qtys_done}} * '''Returns''': sum of "done" quantities for all packages which have been checked in each order ** In other w...") |
m (Woozle moved page VbzCart/VbzCart/queries/qryPkgLines qtys done ord sum to VbzCart/docs/queries/qryPkgLines qtys done ord sum without leaving a redirect: part 2) |
||
| (One intermediate revision by one other user not shown) | |||
| Line 1: | Line 1: | ||
==About== | ==About== | ||
* '''Status''': not useful at this point; filtering ''after'' this stage doesn't speed things up. The filtering has to be done ''before'' the grouping takes place. This query can probably be discarded. | |||
* '''Requires''': {{vbzcart/query|qryPkgLines_qtys_done}} | * '''Requires''': {{vbzcart/query|qryPkgLines_qtys_done}} | ||
* '''Returns''': sum of "done" quantities for all packages which have been checked in each order | * '''Returns''': sum of "done" quantities for all packages which have been checked in each order | ||
** In other words, you get one record for each item in an order; that record gives the sum of quantities "done" for that item across all "checked" packages. This lets you know how many of each item remain open (to be filled). | ** In other words, you get one record for each item in an order; that record gives the sum of quantities "done" for that item across all "checked" packages. This lets you know how many of each item remain open (to be filled). | ||
** This | ** This was intended to then be filtered by ID_Order, so it wouldn't be pulling in a lot of data it doesn't need, but apparently MySQL isn't clever enough to figure that out; it still takes 30-40 seconds even when filtering for just one order. | ||
** There is also PHP code somewhere which does this, but I needed to be able to pull these numbers up in w3tpl. | ** There is also PHP code somewhere which does this, but I needed to be able to pull these numbers up in w3tpl. | ||
* '''History''': | * '''History''': | ||
Latest revision as of 01:55, 25 February 2024
About
- Status: not useful at this point; filtering after this stage doesn't speed things up. The filtering has to be done before the grouping takes place. This query can probably be discarded.
- Requires: Template:Vbzcart/query
- Returns: sum of "done" quantities for all packages which have been checked in each order
- In other words, you get one record for each item in an order; that record gives the sum of quantities "done" for that item across all "checked" packages. This lets you know how many of each item remain open (to be filled).
- This was intended to then be filtered by ID_Order, so it wouldn't be pulling in a lot of data it doesn't need, but apparently MySQL isn't clever enough to figure that out; it still takes 30-40 seconds even when filtering for just one order.
- There is also PHP code somewhere which does this, but I needed to be able to pull these numbers up in w3tpl.
- History:
- 2011-05-31 created for packing slips
SQL
<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryPkgLines_qtys_done_ord_sum AS SELECT
ID_Order, ID_OrdLine, ID_Item, SUM(QtyDone) AS QtyDone
FROM qryPkgLines_qtys_done GROUP BY ID_Order, ID_OrdLine, ID_Item;</mysql> <section end=sql />