Difference between revisions of "VbzCart/queries/qryItTypsDepts grpItems"
Jump to navigation
Jump to search
(moved from single-page listing) |
(4/12 no longer caching stock qty in cat_items; requirements list) |
||
| Line 1: | Line 1: | ||
| + | ==About== | ||
| + | * '''Requires''': {{vbzcart/table|cat_items}}, {{vbzcart/table|cat_titles}}, {{vbzcart/query|v_stk_items_remaining}} | ||
| + | * '''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 | ||
==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 | ||
| − | ID_ItTyp, ID_Dept, | + | i.ID_ItTyp, t.ID_Dept, |
| − | + | ||
| − | SUM(IF(isForSale,1,0)) AS cntForSale, | + | SUM(IF(i.isForSale,1,0)) AS cntForSale, |
| − | SUM(IF(isInPrint,1,0)) AS cntInPrint, | + | SUM(IF(i.isInPrint,1,0)) AS cntInPrint, |
| − | SUM(IF( | + | /* SUM(IF(st.QtyForSale>0,1,0)) AS cntStkForSale, */ |
| − | SUM( | + | SUM(st.qtyForSale) AS qtyForSale |
| − | FROM cat_items AS i LEFT JOIN cat_titles AS t ON i.ID_Title=t.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 ID_ItTyp, ID_Dept | + | GROUP BY i.ID_ItTyp, t.ID_Dept |
HAVING cntForSale;</mysql> | HAVING cntForSale;</mysql> | ||
<section end=sql /> | <section end=sql /> | ||
Revision as of 01:05, 13 April 2009
About
- Requires:
- REDIRECT Template:l/vc/table, cat_titles,
- REDIRECT Template:l/vc/query
- 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
SQL
<mysql>CREATE OR REPLACE VIEW qryItTypsDepts_grpItems AS SELECT
i.ID_ItTyp, t.ID_Dept, SUM(IF(i.isForSale,1,0)) AS cntForSale, SUM(IF(i.isInPrint,1,0)) AS cntInPrint, /* SUM(IF(st.QtyForSale>0,1,0)) AS cntStkForSale, */ SUM(st.qtyForSale) AS qtyForSale
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 HAVING cntForSale;</mysql>