VbzCart/queries/qryItTypsDepts grpItems - Revision history

Woozle: added ID_Supplier

2009-11-30T01:32:11Z

added ID_Supplier

← Older revision		Revision as of 01:32, 30 November 2009
Line 1:		Line 1:
	==About==		==About==
	* '''Requires''': {{vbzcart/query\|qryCat_Items_Stock}}, {{vbzcart/table\|cat_titles}}		* '''Requires''': {{vbzcart/query\|qryCat_Items_Stock}}, {{vbzcart/table\|cat_titles}}, {{vbzcart/table\|cat_depts}}
	* '''Used by''': {{vbzcart/query\|qryItTypsDepts_ItTyps}}		* '''Used by''': {{vbzcart/query\|qryItTypsDepts_ItTyps}}
	* '''History''':		* '''History''':
	** '''2009-04-12''' No longer caching stock-quantity fields in cat_items, so had to pull in <s>v_stk_items_remaining</s> qryCat_Items_Stock; removing cntStkForSale until I find out what it is needed for		** '''2009-04-12''' No longer caching stock-quantity fields in cat_items, so had to pull in <s>v_stk_items_remaining</s> qryCat_Items_Stock; removing cntStkForSale until I find out what it is needed for
			** '''2009-11-29''' Adding ID_Supplier for more efficient coding (see {{vbzcart/query\|qryItTypsDepts_ItTyps}})
			*** Also removing commented-out cntStkForSale
	* '''Notes''':		* '''Notes''':
	** This is essentially the same query as {{vbzcart/query\|qryTitles_ItTyps_grpItems}} but grouped by ID_Dept (and ID_ItTyp) instead of ID_Title (and ID_ItTyp).		** This is essentially the same query as {{vbzcart/query\|qryTitles_ItTyps_grpItems}} but grouped by ID_Dept (and ID_ItTyp) instead of ID_Title (and ID_ItTyp).
Line 9:		Line 11:
	<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryItTypsDepts_grpItems AS		<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryItTypsDepts_grpItems AS
	SELECT		SELECT
	i.ID_ItTyp, t.ID_Dept,		i.ID_ItTyp, t.ID_Dept, d.ID_Supplier,

	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(IF(i.QtyForSale>0,1,0)) AS cntStkForSale, */~~
	SUM(i.qtyForSale) AS qtyForSale		SUM(i.qtyForSale) AS qtyForSale
	FROM qryCat_Items_Stock AS i LEFT JOIN cat_titles AS t ON i.ID_Title=t.ID		FROM (qryCat_Items_Stock AS i LEFT JOIN cat_titles AS t ON i.ID_Title=t.ID) LEFT JOIN cat_depts AS d ON t.ID_Dept=d.ID
	GROUP BY i.ID_ItTyp, t.ID_Dept		GROUP BY i.ID_ItTyp, t.ID_Dept
	HAVING cntForSale;</mysql>		HAVING cntForSale;</mysql>
	<section end=sql />		<section end=sql />

Woozle: note about similarity to other query

2009-04-13T10:50:58Z

note about similarity to other query

← Older revision		Revision as of 10:50, 13 April 2009
Line 4:		Line 4:
	* '''History''':		* '''History''':
	** '''2009-04-12''' No longer caching stock-quantity fields in cat_items, so had to pull in <s>v_stk_items_remaining</s> qryCat_Items_Stock; removing cntStkForSale until I find out what it is needed for		** '''2009-04-12''' No longer caching stock-quantity fields in cat_items, so had to pull in <s>v_stk_items_remaining</s> qryCat_Items_Stock; removing cntStkForSale until I find out what it is needed for
			* '''Notes''':
			** This is essentially the same query as {{vbzcart/query\|qryTitles_ItTyps_grpItems}} but grouped by ID_Dept (and ID_ItTyp) instead of ID_Title (and ID_ItTyp).
	==SQL==		==SQL==
	<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryItTypsDepts_grpItems AS		<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryItTypsDepts_grpItems AS

Woozle: using sub-query instead of doing the JOIN right here

2009-04-13T01:29:00Z

using sub-query instead of doing the JOIN right here

← Older revision		Revision as of 01:29, 13 April 2009
Line 1:		Line 1:
	==About==		==About==
	* '''Requires''': {{vbzcart/~~table~~\|~~cat_items~~}}, {{vbzcart/table\|cat_titles~~}}, {{vbzcart/query\|v_stk_items_remaining~~}}		* '''Requires''': {{vbzcart/query\|qryCat_Items_Stock}}, {{vbzcart/table\|cat_titles}}
	* '''Used by''': {{vbzcart/query\|qryItTypsDepts_ItTyps}}		* '''Used by''': {{vbzcart/query\|qryItTypsDepts_ItTyps}}
	* '''History''':		* '''History''':
	** '''2009-04-12''' No longer caching stock-quantity fields in cat_items, so had to pull in v_stk_items_remaining; removing cntStkForSale until I find out what it is needed for		** '''2009-04-12''' No longer caching stock-quantity fields in cat_items, so had to pull in <s>v_stk_items_remaining</s> qryCat_Items_Stock; removing cntStkForSale until I find out what it is needed for
	==SQL==		==SQL==
	<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryItTypsDepts_grpItems AS		<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryItTypsDepts_grpItems AS
Line 11:		Line 11:
	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(IF(st.QtyForSale>0,1,0)) AS cntStkForSale, */		/* SUM(IF(i.QtyForSale>0,1,0)) AS cntStkForSale, */
	SUM(st.qtyForSale) AS qtyForSale		SUM(i.qtyForSale) AS qtyForSale
	FROM ~~(cat_items AS i LEFT JOIN v_stk_items_remaining~~ AS ~~st on~~ i~~.ID=st.ID_Item)~~ LEFT JOIN cat_titles AS t ON i.ID_Title=t.ID		FROM qryCat_Items_Stock AS i LEFT JOIN cat_titles AS t ON i.ID_Title=t.ID
	GROUP BY i.ID_ItTyp, t.ID_Dept		GROUP BY i.ID_ItTyp, t.ID_Dept
	HAVING cntForSale;</mysql>		HAVING cntForSale;</mysql>
	<section end=sql />		<section end=sql />

Woozle: used by

2009-04-13T01:12:14Z

used by

← Older revision		Revision as of 01:12, 13 April 2009
Line 1:		Line 1:
	==About==		==About==
	* '''Requires''': {{vbzcart/table\|cat_items}}, {{vbzcart/table\|cat_titles}}, {{vbzcart/query\|v_stk_items_remaining}}		* '''Requires''': {{vbzcart/table\|cat_items}}, {{vbzcart/table\|cat_titles}}, {{vbzcart/query\|v_stk_items_remaining}}
			* '''Used by''': {{vbzcart/query\|qryItTypsDepts_ItTyps}}
	* '''History''':		* '''History''':
	** '''2009-04-12''' No longer caching stock-quantity fields in cat_items, so had to pull in v_stk_items_remaining; removing cntStkForSale until I find out what it is needed for		** '''2009-04-12''' No longer caching stock-quantity fields in cat_items, so had to pull in v_stk_items_remaining; removing cntStkForSale until I find out what it is needed for

Woozle: 4/12 no longer caching stock qty in cat_items; requirements list

2009-04-13T01:05:39Z

4/12 no longer caching stock qty in cat_items; requirements list

← Older revision		Revision as of 01:05, 13 April 2009
Line 1:		Line 1:
			==About==
			* '''Requires''': {{vbzcart/table\|cat_items}}, {{vbzcart/table\|cat_titles}}, {{vbzcart/query\|v_stk_items_remaining}}
			* '''History''':
			** '''2009-04-12''' No longer caching stock-quantity fields in cat_items, so had to pull in v_stk_items_remaining; removing cntStkForSale until I find out what it is needed for
	==SQL==		==SQL==
	<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryItTypsDepts_grpItems AS		<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryItTypsDepts_grpItems AS
	SELECT		SELECT
	ID_ItTyp, ID_Dept,		i.ID_ItTyp, t.ID_Dept,

	SUM(IF(isForSale,1,0)) AS cntForSale,		SUM(IF(i.isForSale,1,0)) AS cntForSale,
	SUM(IF(isInPrint,1,0)) AS cntInPrint,		SUM(IF(i.isInPrint,1,0)) AS cntInPrint,
	SUM(IF(~~qtyInStock~~>0,1,0)) AS ~~cntInStock~~,		/* SUM(IF(st.QtyForSale>0,1,0)) AS cntStkForSale, */
	SUM(~~qtyInStock~~) AS ~~qtyInStock~~		SUM(st.qtyForSale) AS qtyForSale
	FROM cat_items AS i LEFT JOIN cat_titles AS t ON i.ID_Title=t.ID		FROM (cat_items AS i LEFT JOIN v_stk_items_remaining AS st on i.ID=st.ID_Item) LEFT JOIN cat_titles AS t ON i.ID_Title=t.ID
	GROUP BY ID_ItTyp, ID_Dept		GROUP BY i.ID_ItTyp, t.ID_Dept
	HAVING cntForSale;</mysql>		HAVING cntForSale;</mysql>
	<section end=sql />		<section end=sql />

Woozle: moved from single-page listing

2008-11-30T18:40:10Z

moved from single-page listing

New page

==SQL==
<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryItTypsDepts_grpItems AS
SELECT
ID_ItTyp, ID_Dept,

SUM(IF(isForSale,1,0)) AS cntForSale,
SUM(IF(isInPrint,1,0)) AS cntInPrint,
SUM(IF(qtyInStock>0,1,0)) AS cntInStock,
SUM(qtyInStock) AS qtyInStock
FROM cat_items AS i LEFT JOIN cat_titles AS t ON i.ID_Title=t.ID
GROUP BY ID_ItTyp, ID_Dept
HAVING cntForSale;</mysql>
<section end=sql />