Difference between revisions of "VbzCart/procs/Upd DeptIttyps fr TitleIttyps"
Jump to navigation
Jump to search
(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 ...) |
m (GROUP clause fix) |
||
(2 intermediate revisions by the same user not shown) | |||
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''': | ||
+ | ** '''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 {{vbzcart|table|cat_depts}} so we can update new field '''ID_Supp''' | ||
==SQL== | ==SQL== | ||
− | <section begin=sql /><mysql>CREATE PROCEDURE Upd_DeptIttyps_fr_TitleIttyps() | + | <section begin=sql /><mysql>DROP PROCEDURE IF EXISTS Upd_DeptIttyps_fr_TitleIttyps; |
− | REPLACE INTO _dept_ittyps(ID_ItTyp,ID_Dept,cntForSale,cntInPrint,qtyInStock) | + | CREATE PROCEDURE Upd_DeptIttyps_fr_TitleIttyps() |
+ | 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_Supplier;</mysql> | ||
<section end=sql /> | <section end=sql /> |
Latest revision as of 19:51, 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_Supplier;</mysql>