Difference between revisions of "VbzCart/queries/qryCat Titles Item stats"
Jump to navigation
Jump to search
(moved from single-page listing) |
(usage update) |
||
| (One intermediate revision by the same user not shown) | |||
| Line 1: | Line 1: | ||
==Details== | ==Details== | ||
| − | This query [[../deprecated|replaces]] v_titles and is used by [[../qryCat_Titles_web|qryCat_Titles_web]]. | + | * '''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''': | ||
| + | ** '''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]]. | ||
==SQL== | ==SQL== | ||
<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryCat_Titles_Item_stats AS | <section begin=sql /><mysql>CREATE OR REPLACE VIEW qryCat_Titles_Item_stats AS | ||
| Line 9: | Line 14: | ||
SUM(IF(i.isForSale,1,0)) AS cntForSale, | SUM(IF(i.isForSale,1,0)) AS cntForSale, | ||
SUM(IF(i.isInPrint,1,0)) AS cntInPrint, | SUM(IF(i.isInPrint,1,0)) AS cntInPrint, | ||
| − | SUM(i. | + | SUM(i.qtyForSale) AS qtyForSale, |
MIN(i.PriceSell) AS currMinSell, | MIN(i.PriceSell) AS currMinSell, | ||
MAX(i.PriceSell) AS currMaxSell, | MAX(i.PriceSell) AS currMaxSell, | ||
| Line 16: | Line 21: | ||
LOWER(CONCAT_WS("/",d.CatWeb_Title,t.CatKey)) AS CatWeb, | LOWER(CONCAT_WS("/",d.CatWeb_Title,t.CatKey)) AS CatWeb, | ||
t.Name | t.Name | ||
| − | FROM ( | + | 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> | GROUP BY i.ID_Title;</mysql> | ||
<section end=sql /> | <section end=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>