Difference between revisions of "VbzCart/queries/qryCat Titles Item stats"
Jump to navigation
Jump to search
(4/12 update; more doc) |
(usage update) |
||
| Line 1: | Line 1: | ||
==Details== | ==Details== | ||
* '''Requires''': {{vbzcart/query|qryCat_Items_Stock}}, {{vbzcart/table|cat_titles}}, {{vbzcart/query|qryCat_Depts}} | * '''Requires''': {{vbzcart/query|qryCat_Items_Stock}}, {{vbzcart/table|cat_titles}}, {{vbzcart/query|qryCat_Depts}} | ||
| + | * '''Used by''': {{vbzcart/query|qryTitles_ItTyps_Titles}}, {{vbzcart/query|qryCat_Titles_web}} (may be unused) | ||
* '''History''': | * '''History''': | ||
** '''2009-04-12''' updated to use qryCat_Items_Stock instead of depending on deprecated cat_items.qtyInStock field | ** '''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 [[../deprecated|replaces]] v_titles and is used by [[../qryCat_Titles_web|qryCat_Titles_web]]. | * '''Note''': This query [[../deprecated|replaces]] v_titles and is used by [[../qryCat_Titles_web|qryCat_Titles_web]]. | ||
==SQL== | ==SQL== | ||
Latest revision as of 19:24, 7 November 2010
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>