VbzCart/procs/Upd DeptIttyps fr TitleIttyps

from HTYP, the free directory anyone can edit if they can prove to me that they're not a spambot
< VbzCart‎ | procs
Revision as of 19:51, 2 February 2011 by Woozle (talk | contribs) (GROUP clause fix)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to navigation Jump to search

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

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>

Retrieved from "https://htyp.org/mw/index.php?title=VbzCart/procs/Upd_DeptIttyps_fr_TitleIttyps&oldid=14928"