VbzCart/procs/Upd SupplierIttyps - Revision history

Woozle: AND (it.ID IS NOT NULL)

2011-02-02T20:04:15Z

AND (it.ID IS NOT NULL)

← Older revision		Revision as of 20:04, 2 February 2011
Line 7:		Line 7:
	*** Added DROP PROCEDURE for easier maintenance.		*** Added DROP PROCEDURE for easier maintenance.
	*** Created in database (procs were not ported from L48 to Rizzo) (why did this one work without any DELIMITER commands?).		*** Created in database (procs were not ported from L48 to Rizzo) (why did this one work without any DELIMITER commands?).
	** '''2011-02-02''' Added WHERE tc.cntForSale>0 so it wouldn't count titles with nothing for sale (duh).		** '''2011-02-02'''
			*** Added WHERE tc.cntForSale>0 so it wouldn't count titles with nothing for sale (duh).
			*** Added AND (it.ID IS NOT NULL) -- input data evidently includes some rows where Item Type is NULL. This needs to be fixed.
	==SQL==		==SQL==
	<section begin=sql /><mysql>DROP PROCEDURE IF EXISTS Upd_SupplierIttyps;		<section begin=sql /><mysql>DROP PROCEDURE IF EXISTS Upd_SupplierIttyps;
Line 23:		Line 25:
	LEFT JOIN _title_ittyps AS ti ON ti.ID_title=tc.ID)		LEFT JOIN _title_ittyps AS ti ON ti.ID_title=tc.ID)
	LEFT JOIN cat_ittyps AS it ON ti.ID_ItTyp=it.ID		LEFT JOIN cat_ittyps AS it ON ti.ID_ItTyp=it.ID
	WHERE tc.cntForSale>0		WHERE (tc.cntForSale>0) AND (it.ID IS NOT NULL)
	GROUP BY s.ID, s.Name, s.CatKey, it.NameSng, it.NamePlr, ID_Parent		GROUP BY s.ID, s.Name, s.CatKey, it.NameSng, it.NamePlr, ID_Parent
	HAVING SUM(tc.cntForSale)		HAVING SUM(tc.cntForSale)

Woozle: WHERE tc.cntForSale>0

2011-02-02T19:57:55Z

WHERE tc.cntForSale>0

← Older revision		Revision as of 19:57, 2 February 2011
Line 7:		Line 7:
	*** Added DROP PROCEDURE for easier maintenance.		*** Added DROP PROCEDURE for easier maintenance.
	*** Created in database (procs were not ported from L48 to Rizzo) (why did this one work without any DELIMITER commands?).		*** Created in database (procs were not ported from L48 to Rizzo) (why did this one work without any DELIMITER commands?).
			** '''2011-02-02''' Added WHERE tc.cntForSale>0 so it wouldn't count titles with nothing for sale (duh).
	==SQL==		==SQL==
	<section begin=sql /><mysql>DROP PROCEDURE IF EXISTS Upd_SupplierIttyps;		<section begin=sql /><mysql>DROP PROCEDURE IF EXISTS Upd_SupplierIttyps;
Line 22:		Line 23:
	LEFT JOIN _title_ittyps AS ti ON ti.ID_title=tc.ID)		LEFT JOIN _title_ittyps AS ti ON ti.ID_title=tc.ID)
	LEFT JOIN cat_ittyps AS it ON ti.ID_ItTyp=it.ID		LEFT JOIN cat_ittyps AS it ON ti.ID_ItTyp=it.ID
			WHERE tc.cntForSale>0
	GROUP BY s.ID, s.Name, s.CatKey, it.NameSng, it.NamePlr, ID_Parent		GROUP BY s.ID, s.Name, s.CatKey, it.NameSng, it.NamePlr, ID_Parent
	HAVING SUM(tc.cntForSale)		HAVING SUM(tc.cntForSale)

Woozle: created in db

2010-11-10T15:15:00Z

created in db

← Older revision		Revision as of 15:15, 10 November 2010
Line 3:		Line 3:
	* '''Output''': {{vbzcart\|table\|_supplier_ittyps}} (delete/insert)		* '''Output''': {{vbzcart\|table\|_supplier_ittyps}} (delete/insert)
	* '''Notes''': Later we might refine this into a way to view unavailable suppliers as well... but that's very low priority, and it might be better to have a separate table for it anyway (keep "active" operations fast).		* '''Notes''': Later we might refine this into a way to view unavailable suppliers as well... but that's very low priority, and it might be better to have a separate table for it anyway (keep "active" operations fast).
			* '''History''':
			** '''2010-11-10'''
			*** Added DROP PROCEDURE for easier maintenance.
			*** Created in database (procs were not ported from L48 to Rizzo) (why did this one work without any DELIMITER commands?).
	==SQL==		==SQL==
	<section begin=sql /><mysql> CREATE PROCEDURE Upd_SupplierIttyps()		<section begin=sql /><mysql>DROP PROCEDURE IF EXISTS Upd_SupplierIttyps;
			CREATE PROCEDURE Upd_SupplierIttyps()
	BEGIN		BEGIN
	DELETE FROM _supplier_ittyps;		DELETE FROM _supplier_ittyps;

Woozle: New page: ==About== * '''Input''': {{vbzcart|table|cat_supp}}, {{vbzcart|table|_titles}}, {{vbzcart|table|_title_ittyps}}, {{vbzcart|table|cat_ittyps}} * '''Output''': {{vbzcart|table|_supplier_itty...

2009-03-08T20:40:12Z

New page: ==About== * '''Input''': {{vbzcart|table|cat_supp}}, {{vbzcart|table|_titles}}, {{vbzcart|table|_title_ittyps}}, {{vbzcart|table|cat_ittyps}} * '''Output''': {{vbzcart|table|_supplier_itty...

New page

==About==
* '''Input''': {{vbzcart|table|cat_supp}}, {{vbzcart|table|_titles}}, {{vbzcart|table|_title_ittyps}}, {{vbzcart|table|cat_ittyps}}
* '''Output''': {{vbzcart|table|_supplier_ittyps}} (delete/insert)
* '''Notes''': Later we might refine this into a way to view unavailable suppliers as well... but that's very low priority, and it might be better to have a separate table for it anyway (keep "active" operations fast).
==SQL==
<section begin=sql /><mysql> CREATE PROCEDURE Upd_SupplierIttyps()
BEGIN
DELETE FROM _supplier_ittyps;
INSERT INTO _supplier_ittyps
SELECT
s.ID,
if(Count(ti.ID_Title)=1,it.NameSng,it.NamePlr) AS ItemType,
Count(ti.ID_Title) AS ItemCount,
s.Name, s.CatKey
FROM (
(cat_supp AS s LEFT JOIN _titles AS tc ON tc.ID_Supp=s.ID)
LEFT JOIN _title_ittyps AS ti ON ti.ID_title=tc.ID)
LEFT JOIN cat_ittyps AS it ON ti.ID_ItTyp=it.ID
GROUP BY s.ID, s.Name, s.CatKey, it.NameSng, it.NamePlr, ID_Parent
HAVING SUM(tc.cntForSale)
ORDER BY s.Name, SUM(tc.cntForSale) DESC;
END</mysql>
<section end=sql />