Difference between revisions of "VbzCart/queries/qryCat Titles Item stats"

from HTYP, the free directory anyone can edit if they can prove to me that they're not a spambot
Jump to navigation Jump to search
(moved from single-page listing)
 
(usage update)
 
(One intermediate revision by the same user not shown)
Line 1: Line 1:
 
==Details==
 
==Details==
This query [[../deprecated|replaces]] v_titles and is used by [[../qryCat_Titles_web|qryCat_Titles_web]].
+
* '''Requires''': {{vbzcart/query|qryCat_Items_Stock}}, {{vbzcart/table|cat_titles}}, {{vbzcart/query|qryCat_Depts}}
 +
* '''Used by''': {{vbzcart/query|qryTitles_ItTyps_Titles}}, {{vbzcart/query|qryCat_Titles_web}} (may be unused)
 +
* '''History''':
 +
** '''2009-04-12''' updated to use qryCat_Items_Stock instead of depending on deprecated cat_items.qtyInStock field
 +
** '''2010-11-07''' not being used by Dept admin page anymore
 +
* '''Note''': This query [[../deprecated|replaces]] v_titles and is used by [[../qryCat_Titles_web|qryCat_Titles_web]].
 
==SQL==
 
==SQL==
 
<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryCat_Titles_Item_stats AS
 
<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryCat_Titles_Item_stats AS
Line 9: Line 14:
 
   SUM(IF(i.isForSale,1,0)) AS cntForSale,
 
   SUM(IF(i.isForSale,1,0)) AS cntForSale,
 
   SUM(IF(i.isInPrint,1,0)) AS cntInPrint,
 
   SUM(IF(i.isInPrint,1,0)) AS cntInPrint,
   SUM(i.qtyInStock) AS qtyInStock,
+
   SUM(i.qtyForSale) AS qtyForSale,
 
   MIN(i.PriceSell) AS currMinSell,
 
   MIN(i.PriceSell) AS currMinSell,
 
   MAX(i.PriceSell) AS currMaxSell,
 
   MAX(i.PriceSell) AS currMaxSell,
Line 16: Line 21:
 
   LOWER(CONCAT_WS("/",d.CatWeb_Title,t.CatKey)) AS CatWeb,
 
   LOWER(CONCAT_WS("/",d.CatWeb_Title,t.CatKey)) AS CatWeb,
 
   t.Name
 
   t.Name
FROM (cat_items AS i LEFT JOIN cat_titles AS t ON i.ID_Title=t.ID) LEFT JOIN qryCat_Depts AS d ON t.ID_dept=d.ID
+
FROM (qryCat_Items_Stock AS i LEFT JOIN cat_titles AS t ON i.ID_Title=t.ID) LEFT JOIN qryCat_Depts AS d ON t.ID_dept=d.ID
 
GROUP BY i.ID_Title;</mysql>
 
GROUP BY i.ID_Title;</mysql>
 
<section end=sql />
 
<section end=sql />

Latest revision as of 19:24, 7 November 2010

Details

  • Requires:
  1. REDIRECT Template:l/vc/query,
  2. REDIRECT Template:l/vc/table, qryCat_Depts

SQL

<mysql>CREATE OR REPLACE VIEW qryCat_Titles_Item_stats AS SELECT

 ID_Title,
 ID_Dept,
 DateAdded AS WhenAdded,
 SUM(IF(i.isForSale,1,0)) AS cntForSale,
 SUM(IF(i.isInPrint,1,0)) AS cntInPrint,
 SUM(i.qtyForSale) AS qtyForSale,
 MIN(i.PriceSell) AS currMinSell,
 MAX(i.PriceSell) AS currMaxSell,
 d.ID_Supplier,
 UPPER(CONCAT_WS("-",d.CatNum,t.CatKey)) AS CatNum,
 LOWER(CONCAT_WS("/",d.CatWeb_Title,t.CatKey)) AS CatWeb,
 t.Name

FROM (qryCat_Items_Stock AS i LEFT JOIN cat_titles AS t ON i.ID_Title=t.ID) LEFT JOIN qryCat_Depts AS d ON t.ID_dept=d.ID GROUP BY i.ID_Title;</mysql>