Difference between revisions of "VbzCart/procs/Upd DeptIttyps fr TitleIttyps"
Jump to navigation
Jump to search
(created in db) |
(+ID_Supp) |
||
| 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 /> | ||
Revision as of 17:04, 2 February 2011
About
- Input: _title_ittyps, cat_depts (grouped by ID_ItTyp, ID_Dept, (ID_Supp redundant))
- Output: _dept_ittyps (replace)
- History:
- 2010-11-10
- Added DROP PROCEDURE for easier maintenance.
- Created in database (procs were not ported from L48 to Rizzo).
- 2011-02-02 added join with cat_depts so we can update new field ID_Supp
- 2010-11-10
SQL
<mysql>DROP PROCEDURE IF EXISTS Upd_DeptIttyps_fr_TitleIttyps; CREATE PROCEDURE Upd_DeptIttyps_fr_TitleIttyps()
REPLACE INTO _dept_ittyps(ID_ItTyp,ID_Dept,ID_Supp,cntForSale,cntInPrint,qtyInStock) SELECT ti.ID_ItTyp, ti.ID_Dept, d.ID_Supplier, SUM(ti.cntForSale) AS cntForSale, SUM(ti.cntInPrint) AS cntInPrint, SUM(ti.qtyInStock) AS qtyInStock 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>