VbzCart/queries/qryCat Titles Item count - Revision history

Woozle: refinements to maintenance query

2010-11-11T22:58:21Z

refinements to maintenance query

@@ Line 31: / Line 31: @@
    LEFT JOIN qryCat_Titles_Item_count AS t
    ON t.ID_Title=i.ID_Title
-   WHERE (CatSfx IS NULL) AND (NOT isPulled) AND (cntItems>1);</mysql>
+   WHERE (i.CatSfx IS NULL) AND (NOT i.isPulled) AND (t.cntItems>1) AND (CatNum IS NOT NULL)

Woozle: formatting

2010-11-11T22:42:44Z

formatting

← Older revision		Revision as of 22:42, 11 November 2010
Line 4:		Line 4:
	* '''Note''': This is very similar to {{vbzcart/query\|qryCat_Titles_Item_stats}}, but does not bring in stock information -- therefore faster and possibly including more records.		* '''Note''': This is very similar to {{vbzcart/query\|qryCat_Titles_Item_stats}}, but does not bring in stock information -- therefore faster and possibly including more records.
	* '''History''':		* '''History''':
	** '''2010-11-11 Created.		** '''2010-11-11''' Created.
	==SQL==		==SQL==
	<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryCat_Titles_Item_count AS		<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryCat_Titles_Item_count AS

Woozle: Created page with '==About== * '''Purpose''': Subquery to count items for each title record * '''Used by''': a one-time maintenance query which ''might'' eventually become part of the catalog-build…'

2010-11-11T22:41:30Z

Created page with '==About== * '''Purpose''': Subquery to count items for each title record * '''Used by''': a one-time maintenance query which ''might'' eventually become part of the catalog-build…'

New page

==About==
* '''Purpose''': Subquery to count items for each title record
* '''Used by''': a one-time maintenance query which ''might'' eventually become part of the catalog-building process. The objective was to be able to distinguish between (a) items which need a CatSfx added to the Title's CatKey because there are more than one of them per title. and (b) items whose catalog numbers can be generated without a CatSfx. If an item falls into the former category but its CatSfx is NULL, this represents a maintenance issue -- probably leftover data from before that field was created, but might also indicate a bug in the catalog building.
* '''Note''': This is very similar to {{vbzcart/query|qryCat_Titles_Item_stats}}, but does not bring in stock information -- therefore faster and possibly including more records.
* '''History''':
** '''2010-11-11 Created.
==SQL==
<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryCat_Titles_Item_count AS
SELECT
ID_Title,
ID_Dept,
DateAdded AS WhenAdded,
COUNT(i.ID) AS cntItems,
SUM(IF(i.isForSale,1,0)) AS cntForSale,
SUM(IF(i.isInPrint,1,0)) AS cntInPrint,
MIN(i.PriceSell) AS currMinSell,
MAX(i.PriceSell) AS currMaxSell,
t.Name
FROM cat_items AS i LEFT JOIN cat_titles AS t ON i.ID_Title=t.ID
GROUP BY i.ID_Title;</mysql>
<section end=sql />
===maintenance query===
This is the temporary query for which this query was created:
<mysql>SELECT
i.ID,
i.ID_Title,
i.CatNum,
i.CatSfx,
t.cntItems
FROM cat_items AS i
LEFT JOIN qryCat_Titles_Item_count AS t
ON t.ID_Title=i.ID_Title
WHERE (CatSfx IS NULL) AND (NOT isPulled) AND (cntItems>1);</mysql>