VbzCart/procs/Upd TitleIttyps fr CatItems Titles

About

 * Purpose: fills.
 * Input:, ,
 * Output: (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
 * 2010-11-10
 * Added DROP PROCEDURE for easier maintenance.
 * Actually created in database (procs did not get ported from L48 to Rizzo).
 * 2010-11-13 Source field qtyInStock was renamed to qtyIn_Stk some time ago...
 * Added cntInPrint, cntLines
 * Added (ID_Dept IS NOT NULL) AND (ID_ItTyp IS NOT NULL) to prevent those fields being null (not allowed in target table)
 * 2011-01-23 fetching Stock data from instead of relying on  to be current
 * 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
DROP PROCEDURE IF EXISTS Upd_TitleIttyps_fr_CatItems_Titles; CREATE PROCEDURE Upd_TitleIttyps_fr_CatItems_Titles REPLACE INTO _title_ittyps(     ID_Title,      ID_ItTyp,      ID_Dept,      TitleName,      cntLines,      cntCurrent,      cntInPrint,      cntForSale,      cntInStock,      qtyInStock,      currMinPrice,      currMaxPrice) SELECT i.ID_Title, i.ID_ItTyp, t.ID_Dept, t.Name AS TitleName, i.cntLines, i.cntCurrent, i.cntInPrint, i.cntForSale, i.cntInStock, i.qtyInStock, i.currMinPrice, i.currMaxPrice FROM (      SELECT         ID_Title, ID_ItTyp,         COUNT(ID) AS cntLines,         SUM(IF(isCurrent,1,0)) AS cntCurrent,         SUM(IF(isInPrint,1,0)) AS cntInPrint,         SUM(IF(isForSale,1,0)) AS cntForSale,         SUM(IF(iq.qtyForSale>0,1,0)) AS cntInStock,         SUM(iq.qtyForSale) AS qtyInStock,         MIN(PriceSell) AS currMinPrice,         MAX(PriceSell) AS currMaxPrice       FROM cat_items AS i LEFT JOIN qryStk_items_remaining AS iq ON i.ID=iq.ID_Item       GROUP BY ID_Title, ID_ItTyp     ) AS i LEFT JOIN cat_titles AS t ON i.ID_Title=t.ID  WHERE (ID_Dept IS NOT NULL) AND (ID_ItTyp IS NOT NULL);