Difference between revisions of "VbzCart/queries/qryItTypsDepts grpItems"

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
(4/12 no longer caching stock qty in cat_items; requirements list)
(added ID_Supplier)
 
(3 intermediate revisions by the same user not shown)
Line 1: Line 1:
 
==About==
 
==About==
−
* '''Requires''': {{vbzcart/table|cat_items}}, {{vbzcart/table|cat_titles}}, {{vbzcart/query|v_stk_items_remaining}}
+
* '''Requires''': {{vbzcart/query|qryCat_Items_Stock}}, {{vbzcart/table|cat_titles}}, {{vbzcart/table|cat_depts}}
 +
* '''Used by''': {{vbzcart/query|qryItTypsDepts_ItTyps}}
 
* '''History''':
 
* '''History''':
−
** '''2009-04-12''' No longer caching stock-quantity fields in cat_items, so had to pull in v_stk_items_remaining; removing cntStkForSale until I find out what it is needed for
+
** '''2009-04-12''' No longer caching stock-quantity fields in cat_items, so had to pull in <s>v_stk_items_remaining</s> qryCat_Items_Stock; removing cntStkForSale until I find out what it is needed for
 +
** '''2009-11-29''' Adding ID_Supplier for more efficient coding (see {{vbzcart/query|qryItTypsDepts_ItTyps}})
 +
*** Also removing commented-out cntStkForSale
 +
* '''Notes''':
 +
** This is essentially the same query as {{vbzcart/query|qryTitles_ItTyps_grpItems}} but grouped by ID_Dept (and ID_ItTyp) instead of ID_Title (and ID_ItTyp).
 
==SQL==
 
==SQL==
 
<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryItTypsDepts_grpItems AS
 
<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryItTypsDepts_grpItems AS
 
SELECT
 
SELECT
−
   i.ID_ItTyp, t.ID_Dept,
+
   i.ID_ItTyp, t.ID_Dept, d.ID_Supplier,
 
   
 
   
 
   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(IF(st.QtyForSale>0,1,0)) AS cntStkForSale, */
+
   SUM(i.qtyForSale) AS qtyForSale
−
  SUM(st.qtyForSale) AS qtyForSale
+
FROM (qryCat_Items_Stock AS i LEFT JOIN cat_titles AS t ON i.ID_Title=t.ID) LEFT JOIN cat_depts AS d ON t.ID_Dept=d.ID
−
FROM (cat_items AS i LEFT JOIN v_stk_items_remaining AS st on i.ID=st.ID_Item) LEFT JOIN cat_titles AS t ON i.ID_Title=t.ID
 
 
GROUP BY i.ID_ItTyp, t.ID_Dept
 
GROUP BY i.ID_ItTyp, t.ID_Dept
 
HAVING cntForSale;</mysql>
 
HAVING cntForSale;</mysql>
 
<section end=sql />
 
<section end=sql />

Latest revision as of 01:32, 30 November 2009

About

  • Requires:
  1. REDIRECT Template:l/vc/query,
  2. REDIRECT Template:l/vc/table, cat_depts
  • Used by: qryItTypsDepts_ItTyps
  • History:
    • 2009-04-12 No longer caching stock-quantity fields in cat_items, so had to pull in v_stk_items_remaining qryCat_Items_Stock; removing cntStkForSale until I find out what it is needed for
    • 2009-11-29 Adding ID_Supplier for more efficient coding (see qryItTypsDepts_ItTyps)
      • Also removing commented-out cntStkForSale
  • Notes:
    • This is essentially the same query as qryTitles_ItTyps_grpItems but grouped by ID_Dept (and ID_ItTyp) instead of ID_Title (and ID_ItTyp).

SQL

<mysql>CREATE OR REPLACE VIEW qryItTypsDepts_grpItems AS SELECT 

 i.ID_ItTyp, t.ID_Dept, d.ID_Supplier,

 SUM(IF(i.isForSale,1,0)) AS cntForSale,
 SUM(IF(i.isInPrint,1,0)) AS cntInPrint,
 SUM(i.qtyForSale) AS qtyForSale

FROM (qryCat_Items_Stock AS i LEFT JOIN cat_titles AS t ON i.ID_Title=t.ID) LEFT JOIN cat_depts AS d ON t.ID_Dept=d.ID GROUP BY i.ID_ItTyp, t.ID_Dept HAVING cntForSale;</mysql>

Retrieved from "https://htyp.org/mw/index.php?title=VbzCart/queries/qryItTypsDepts_grpItems&oldid=12701"