VbzCart/procs/Upd DeptIttyps fr TitleIttyps - Revision history

Woozle: GROUP clause fix

2011-02-02T19:51:51Z

GROUP clause fix

← Older revision		Revision as of 19:51, 2 February 2011
Line 19:		Line 19:
	SUM(ti.qtyInStock) AS qtyInStock		SUM(ti.qtyInStock) AS qtyInStock
	FROM _title_ittyps AS ti LEFT JOIN cat_depts AS d ON ti.ID_Dept=d.ID		FROM _title_ittyps AS ti LEFT JOIN cat_depts AS d ON ti.ID_Dept=d.ID
	GROUP BY ID_ItTyp, ID_Dept, ~~ID_Supp~~;</mysql>		GROUP BY ID_ItTyp, ID_Dept, ID_Supplier;</mysql>
	<section end=sql />		<section end=sql />

Woozle: +ID_Supp

2011-02-02T17:04:49Z

+ID_Supp

← Older revision		Revision as of 17:04, 2 February 2011
Line 1:		Line 1:
	==About==		==About==
	* '''Input''': {{vbzcart\|table\|_title_ittyps}} (grouped by ID_ItTyp, ID_Dept)		* '''Input''': {{vbzcart\|table\|_title_ittyps}}, {{vbzcart\|table\|cat_depts}} (grouped by ID_ItTyp, ID_Dept, (ID_Supp redundant))
	* '''Output''': {{vbzcart\|table\|_dept_ittyps}} (replace)		* '''Output''': {{vbzcart\|table\|_dept_ittyps}} (replace)
	* '''History''':		* '''History''':
Line 6:		Line 6:
	*** Added DROP PROCEDURE for easier maintenance.		*** Added DROP PROCEDURE for easier maintenance.
	*** Created in database (procs were not ported from L48 to Rizzo).		*** Created in database (procs were not ported from L48 to Rizzo).
			** '''2011-02-02''' added join with {{vbzcart\|table\|cat_depts}} so we can update new field '''ID_Supp'''
	==SQL==		==SQL==
	<section begin=sql /><mysql>DROP PROCEDURE IF EXISTS Upd_DeptIttyps_fr_TitleIttyps;		<section begin=sql /><mysql>DROP PROCEDURE IF EXISTS Upd_DeptIttyps_fr_TitleIttyps;
	CREATE PROCEDURE Upd_DeptIttyps_fr_TitleIttyps()		CREATE PROCEDURE Upd_DeptIttyps_fr_TitleIttyps()
	REPLACE INTO _dept_ittyps(ID_ItTyp,ID_Dept,cntForSale,cntInPrint,qtyInStock)		REPLACE INTO _dept_ittyps(ID_ItTyp,ID_Dept,ID_Supp,cntForSale,cntInPrint,qtyInStock)
	SELECT		SELECT
	ID_ItTyp,		ti.ID_ItTyp,
	ID_Dept,		ti.ID_Dept,
	SUM(cntForSale) AS cntForSale,		d.ID_Supplier,
	SUM(cntInPrint) AS cntInPrint,		SUM(ti.cntForSale) AS cntForSale,
	SUM(qtyInStock) AS qtyInStock		SUM(ti.cntInPrint) AS cntInPrint,
	FROM _title_ittyps		SUM(ti.qtyInStock) AS qtyInStock
	GROUP BY ID_ItTyp, ID_Dept;</mysql>		FROM _title_ittyps AS ti LEFT JOIN cat_depts AS d ON ti.ID_Dept=d.ID
			GROUP BY ID_ItTyp, ID_Dept, ID_Supp;</mysql>
	<section end=sql />		<section end=sql />

Woozle: created in db

2010-11-10T15:21:04Z

created in db

← Older revision		Revision as of 15:21, 10 November 2010
Line 2:		Line 2:
	* '''Input''': {{vbzcart\|table\|_title_ittyps}} (grouped by ID_ItTyp, ID_Dept)		* '''Input''': {{vbzcart\|table\|_title_ittyps}} (grouped by ID_ItTyp, ID_Dept)
	* '''Output''': {{vbzcart\|table\|_dept_ittyps}} (replace)		* '''Output''': {{vbzcart\|table\|_dept_ittyps}} (replace)
			* '''History''':
			** '''2010-11-10'''
			*** Added DROP PROCEDURE for easier maintenance.
			*** Created in database (procs were not ported from L48 to Rizzo).
	==SQL==		==SQL==
	<section begin=sql /><mysql>CREATE PROCEDURE Upd_DeptIttyps_fr_TitleIttyps()		<section begin=sql /><mysql>DROP PROCEDURE IF EXISTS Upd_DeptIttyps_fr_TitleIttyps;
			CREATE PROCEDURE Upd_DeptIttyps_fr_TitleIttyps()
	REPLACE INTO _dept_ittyps(ID_ItTyp,ID_Dept,cntForSale,cntInPrint,qtyInStock)		REPLACE INTO _dept_ittyps(ID_ItTyp,ID_Dept,cntForSale,cntInPrint,qtyInStock)
	SELECT		SELECT

Woozle: New page: ==About== * '''Input''': {{vbzcart|table|_title_ittyps}} (grouped by ID_ItTyp, ID_Dept) * '''Output''': {{vbzcart|table|_dept_ittyps}} (replace) ==SQL==
CREATE ...

2009-03-08T20:16:57Z

New page: ==About== * '''Input''': {{vbzcart|table|_title_ittyps}} (grouped by ID_ItTyp, ID_Dept) * '''Output''': {{vbzcart|table|_dept_ittyps}} (replace) ==SQL== <section begin=sql /><mysql>CREATE ...

New page

==About==
* '''Input''': {{vbzcart|table|_title_ittyps}} (grouped by ID_ItTyp, ID_Dept)
* '''Output''': {{vbzcart|table|_dept_ittyps}} (replace)
==SQL==
<section begin=sql /><mysql>CREATE PROCEDURE Upd_DeptIttyps_fr_TitleIttyps()
REPLACE INTO _dept_ittyps(ID_ItTyp,ID_Dept,cntForSale,cntInPrint,qtyInStock)
SELECT
ID_ItTyp,
ID_Dept,
SUM(cntForSale) AS cntForSale,
SUM(cntInPrint) AS cntInPrint,
SUM(qtyInStock) AS qtyInStock
FROM _title_ittyps
GROUP BY ID_ItTyp, ID_Dept;</mysql>
<section end=sql />

VbzCart/procs/Upd DeptIttyps fr TitleIttyps - Revision history

Woozle: GROUP clause fix

Woozle: +ID_Supp

Woozle: created in db

Woozle: New page: ==About== * '''Input''': {{vbzcart|table|_title_ittyps}} (grouped by ID_ItTyp, ID_Dept) * '''Output''': {{vbzcart|table|_dept_ittyps}} (replace) ==SQL== CREATE ...

Woozle: New page: ==About== * '''Input''': {{vbzcart|table|_title_ittyps}} (grouped by ID_ItTyp, ID_Dept) * '''Output''': {{vbzcart|table|_dept_ittyps}} (replace) ==SQL==
CREATE ...