VbzCart/procs/Upd TitleIttyps fr CatItems Titles

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.

SQL

<mysql>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;</mysql>