VbzCart/queries/qryStock by Supp Type Opt

from HTYP, the free directory anyone can edit if they can prove to me that they're not a spambot

< VbzCart‎ | queries

Jump to navigation Jump to search

About

Returns: Stock totals by supplier+itemtype+itemoption
Requires:

REDIRECT Template:l/vc/table,
REDIRECT Template:l/vc/query, cat_ittyps, 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 qryStock_by_Opt_Type (no Supp)
History:
- 2008-12-09 Rewritten almost from scratch (loosely based on existing Access query), not sure how the old one worked

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>

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

vbzcart/unused