Difference between revisions of "VbzCart/queries/qryCat Titles Item stats"
Jump to navigation
Jump to search
(moved from single-page listing) |
(4/12 update; more doc) |
||
| 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}} |
| + | * '''History''': | ||
| + | ** '''2009-04-12''' updated to use qryCat_Items_Stock instead of depending on deprecated cat_items.qtyInStock field | ||
| + | * '''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 12: | ||
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 19: | ||
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 /> | ||
Revision as of 01:36, 13 April 2009
Details
- Requires:
- REDIRECT Template:l/vc/query,
- REDIRECT Template:l/vc/table, qryCat_Depts
- History:
- 2009-04-12 updated to use qryCat_Items_Stock instead of depending on deprecated cat_items.qtyInStock field
- 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>