VbzCart/procs/Upd TitleIttyps fr CatItems Titles

From HTYP, the free directory anyone can edit

[edit] About

• Purpose: fills _title_ittyps.
• Input: cat_items, cat_titles
• Output: _title_ittyps (replace)
• History:
  • 2007-09-20
    • Removed "WHERE i.isForSale" because this prevented the availability of discontinued items from being properly zeroed out
    • Added cntInStock calculation; see table schema
• Notes:
  • This is a revised version which combines two earlier procedures.
  • I had a note that "the ID_Dept field is informational, not a grouping", but I don't remember what that meant and it doesn't make sense.

[edit] SQL

CREATE PROCEDURE Upd_TitleIttyps_fr_CatItems_Titles()
    REPLACE INTO _title_ittyps(
      ID_Title,
      ID_ItTyp,
      ID_Dept,
      TitleName,
      cntForSale,
      cntInPrint,
      cntInStock,
      qtyInStock,
      currMinPrice,
      currMaxPrice)
    SELECT
      i.ID_Title,
      i.ID_ItTyp,
      t.ID_Dept,
      t.Name AS TitleName,
      i.cntForSale,
      i.cntInPrint,
      i.cntInStock,
      i.qtyInStock,
      i.currMinPrice,
      i.currMaxPrice
     FROM (
       SELECT
         ID_Title, ID_ItTyp,
         SUM(IF(isForSale,1,0)) AS cntForSale,
         SUM(IF(isInPrint,1,0)) AS cntInPrint,
         SUM(IF(qtyInStock>0,1,0)) AS cntInStock,
         SUM(qtyInStock) AS qtyInStock,
         MIN(PriceSell) AS currMinPrice,
         MAX(PriceSell) AS currMaxPrice
       FROM cat_items AS i
       GROUP BY ID_Title, ID_ItTyp
     ) AS i LEFT JOIN cat_titles AS t ON i.ID_Title=t.ID;