VbzCart/queries/qryCat Titles Item stats
Jump to navigation
Jump to search
The printable version is no longer supported and may have rendering errors. Please update your browser bookmarks and please use the default browser print function instead.
Details
- Requires:
- REDIRECT Template:l/vc/query,
- REDIRECT Template:l/vc/table, qryCat_Depts
- Used by: qryTitles_ItTyps_Titles, qryCat_Titles_web (may be unused)
- History:
- 2009-04-12 updated to use qryCat_Items_Stock instead of depending on deprecated cat_items.qtyInStock field
- 2010-11-07 not being used by Dept admin page anymore
- Note: This query replaces v_titles and is used by qryCat_Titles_web.
SQL
<mysql>CREATE OR REPLACE VIEW qryCat_Titles_Item_stats AS SELECT
ID_Title,
ID_Dept,
DateAdded AS WhenAdded,
SUM(IF(i.isForSale,1,0)) AS cntForSale,
SUM(IF(i.isInPrint,1,0)) AS cntInPrint,
SUM(i.qtyForSale) AS qtyForSale,
MIN(i.PriceSell) AS currMinSell,
MAX(i.PriceSell) AS currMaxSell,
d.ID_Supplier,
UPPER(CONCAT_WS("-",d.CatNum,t.CatKey)) AS CatNum,
LOWER(CONCAT_WS("/",d.CatWeb_Title,t.CatKey)) AS CatWeb,
t.Name
FROM (qryCat_Items_Stock AS i LEFT JOIN cat_titles AS t ON i.ID_Title=t.ID) LEFT JOIN qryCat_Depts AS d ON t.ID_dept=d.ID GROUP BY i.ID_Title;</mysql>