VbzCart/docs/procs/Upd DeptIttyps fr TitleIttyps: Difference between revisions

From Woozle Writes Code
< VbzCart‎ | docs‎ | procs
Jump to navigation Jump to search
imported>Woozle
(created in db)
imported>Woozle
(+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: Template:Vbzcart, Template:Vbzcart (grouped by ID_ItTyp, ID_Dept, (ID_Supp redundant))
  • Output: Template:Vbzcart (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 Template:Vbzcart so we can update new field ID_Supp

SQL

<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,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>

<section end=sql />

Retrieved from "https://wooz.dev/mw/index.php?title=VbzCart/docs/procs/Upd_DeptIttyps_fr_TitleIttyps&oldid=4845"