VbzCart/docs/queries/qryStock by Supp Type Opt: Difference between revisions
Jump to navigation
Jump to search
imported>Woozle m (→About: update -- this one currently has no use) |
m (Woozle moved page VbzCart/VbzCart/queries/qryStock by Supp Type Opt to VbzCart/docs/queries/qryStock by Supp Type Opt without leaving a redirect: part 3/5) |
||
| (One intermediate revision by one other user not shown) | |||
| Line 3: | Line 3: | ||
* '''Returns''': Stock totals by supplier+itemtype+itemoption | * '''Returns''': Stock totals by supplier+itemtype+itemoption | ||
* '''Requires''': {{vbzcart/table|cat_items}}, {{vbzcart/query|qryCat_Titles}}, {{vbzcart/table|cat_ittyps}}, {{vbzcart/query|qryStkItms_for_sale}} | * '''Requires''': {{vbzcart/table|cat_items}}, {{vbzcart/query|qryCat_Titles}}, {{vbzcart/table|cat_ittyps}}, {{vbzcart/query|qryStkItms_for_sale}} | ||
* '''Used by''': nothing yet; thought this was going to be for the stock-by-size index page, but realized that should be {{vbzcart/query| | * '''Used by''': nothing yet; thought this was going to be for the stock-by-size index page, but realized that should be {{vbzcart/query|qryStock_by_Opt_Type}} (no Supp) | ||
* '''History''': | * '''History''': | ||
** '''2008-12-09''' Rewritten almost from scratch (loosely based on existing Access query), not sure how the old one worked | ** '''2008-12-09''' Rewritten almost from scratch (loosely based on existing Access query), not sure how the old one worked | ||
Latest revision as of 01:56, 25 February 2024
About
- Returns: Stock totals by supplier+itemtype+itemoption
- Requires: Template:Vbzcart/table, Template:Vbzcart/query, Template:Vbzcart/table, Template:Vbzcart/query
- Used by: nothing yet; thought this was going to be for the stock-by-size index page, but realized that should be Template:Vbzcart/query (no Supp)
- History:
- 2008-12-09 Rewritten almost from scratch (loosely based on existing Access query), not sure how the old one worked
SQL
<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryStock_by_Supp_Type_Opt AS SELECT
t.ID_Supplier, io.CatKey, it.NameSng AS TypeSing, IFNULL(it.NamePlr,it.NameSng) AS TypePlur, SUM(stk.QtyForSale) AS QtyForSale, Count(it.ID) AS TypeCount
FROM
(((cat_ioptns AS io
LEFT JOIN cat_items AS i ON i.ID_ItOpt=io.ID)
LEFT JOIN qryCat_Titles AS t ON i.ID_Title=t.ID)
LEFT JOIN cat_ittyps AS it ON i.ID_ItTyp=it.ID)
LEFT JOIN qryStkItms_for_sale AS stk ON i.ID=stk.ID_Item
GROUP BY t.ID_Supplier, it.NamePlr, it.NameSng, io.CatKey HAVING QtyForSale;</mysql> <section end=sql />