VbzCart/queries/qryCat Titles Item stats - Revision history

Woozle: usage update

2010-11-07T19:24:02Z

usage update

← Older revision		Revision as of 19:24, 7 November 2010
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==

Woozle: 4/12 update; more doc

2009-04-13T01:36:31Z

4/12 update; more doc

← Older revision		Revision as of 01:36, 13 April 2009
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.~~qtyInStock~~) AS ~~qtyInStock~~,		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 (~~cat_items~~ 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		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 />

Woozle: moved from single-page listing

2008-11-30T18:45:57Z

moved from single-page listing

New page

==Details==
This query [[../deprecated|replaces]] v_titles and is used by [[../qryCat_Titles_web|qryCat_Titles_web]].
==SQL==
<section begin=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.qtyInStock) AS qtyInStock,
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 (cat_items 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>
<section end=sql />