VbzCart/docs/queries/qryRstkReq Items expected: Difference between revisions
Jump to navigation
Jump to search
imported>Woozle (Created page with '==About== * '''Purpose''': Returns a list of items we're currently expecting to receive in restocks, based on items requested (in active restock requests only) less items already…') |
imported>Woozle (corrected slightly, but deprecated anyway) |
||
| Line 2: | Line 2: | ||
* '''Purpose''': Returns a list of items we're currently expecting to receive in restocks, based on items requested (in active restock requests only) less items already received for those requests | * '''Purpose''': Returns a list of items we're currently expecting to receive in restocks, based on items requested (in active restock requests only) less items already received for those requests | ||
* '''Requires''': {{vbzcart/query|qryRstkReq_Item_status}} | * '''Requires''': {{vbzcart/query|qryRstkReq_Item_status}} | ||
* '''Used by ''': | * '''Used by ''': DEPRECATED - seems less accurate than {{vbzcart/query|qryRstkItms_expected_byItem}} | ||
* '''Related''': Like {{vbzcart/query|qryRstkItms_expected}}, but grouped by Item (eliminates rstk_req data) | * '''Related''': Like {{vbzcart/query|qryRstkItms_expected}}, but grouped by Item (eliminates rstk_req data) | ||
* '''History''': | * '''History''': | ||
** '''2010-01-03''' Created for generating list of needed items | ** '''2010-01-03''' Created for generating list of needed items | ||
** '''2010-01-04''' This query now deprecated and can probably be deleted; using QtyExp instead of QtyOrd for verification of results | |||
* '''Future''': This probably does not take into account the '''QtyExp''' field, which should override the '''QtyOrd''' field. | |||
==SQL== | ==SQL== | ||
<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryRstkReq_Items_expected AS | <section begin=sql /><mysql>CREATE OR REPLACE VIEW qryRstkReq_Items_expected AS | ||
| Line 11: | Line 13: | ||
ID_Item, | ID_Item, | ||
SUM(QtyRecd) AS QtyRecd, | SUM(QtyRecd) AS QtyRecd, | ||
SUM( | SUM(QtyExp) AS QtyExp | ||
FROM qryRstkReq_Item_status | FROM qryRstkReq_Item_status | ||
GROUP BY ID_Item | GROUP BY ID_Item | ||
HAVING SUM( | HAVING SUM(QtyExp)-SUM(QtyRecd)>0;</mysql> | ||
<section end=sql /> | <section end=sql /> | ||
Revision as of 00:16, 6 January 2010
About
- Purpose: Returns a list of items we're currently expecting to receive in restocks, based on items requested (in active restock requests only) less items already received for those requests
- Requires: Template:Vbzcart/query
- Used by : DEPRECATED - seems less accurate than Template:Vbzcart/query
- Related: Like Template:Vbzcart/query, but grouped by Item (eliminates rstk_req data)
- History:
- 2010-01-03 Created for generating list of needed items
- 2010-01-04 This query now deprecated and can probably be deleted; using QtyExp instead of QtyOrd for verification of results
- Future: This probably does not take into account the QtyExp field, which should override the QtyOrd field.
SQL
<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryRstkReq_Items_expected AS SELECT
ID_Item, SUM(QtyRecd) AS QtyRecd, SUM(QtyExp) AS QtyExp
FROM qryRstkReq_Item_status GROUP BY ID_Item HAVING SUM(QtyExp)-SUM(QtyRecd)>0;</mysql> <section end=sql />