VbzCart/queries/qryRstkItms expected - Revision history

Woozle: discarded

2016-03-02T22:56:41Z

discarded

← Older revision		Revision as of 22:56, 2 March 2016
Line 10:		Line 10:
	** '''2010-01-03''' fixed syntax: have to recalculate aggregate functions to use them in a HAVING clause (bleh)		** '''2010-01-03''' fixed syntax: have to recalculate aggregate functions to use them in a HAVING clause (bleh)
	** '''2010-01-04''' updated to use QtyExp instead of QtyOrd (field renamed/changed in {{vbzcart/query\|qryRstkItms_active}})		** '''2010-01-04''' updated to use QtyExp instead of QtyOrd (field renamed/changed in {{vbzcart/query\|qryRstkItms_active}})
			** '''2016-03-02''' Query no longer works; removing from database.
	==SQL==		==SQL==
	~~<section begin=sql />~~<mysql>CREATE OR REPLACE VIEW qryRstkItms_expected AS		<mysql>CREATE OR REPLACE VIEW qryRstkItms_expected AS
	SELECT		SELECT
	rqi.ID_Restock,		rqi.ID_Restock,
Line 23:		Line 24:
	GROUP BY rqi.ID_Restock, rqi.ID_Item		GROUP BY rqi.ID_Restock, rqi.ID_Item
	HAVING (SUM(rqi.QtyExp)-SUM(IFNULL(rcl.QtyRecd,rcl.QtyFiled)))>0;</mysql>		HAVING (SUM(rqi.QtyExp)-SUM(IFNULL(rcl.QtyRecd,rcl.QtyFiled)))>0;</mysql>
	~~<section end=sql />~~

Woozle: used by qryRstkItms_expected_byItem

2010-01-06T00:21:00Z

used by qryRstkItms_expected_byItem

← Older revision		Revision as of 00:21, 6 January 2010
Line 2:		Line 2:
	* '''Returns''': Restock items requested (ordered), not yet received, but possibly expected because the restock request hasn't been terminated (closed, orphaned, or killed).		* '''Returns''': Restock items requested (ordered), not yet received, but possibly expected because the restock request hasn't been terminated (closed, orphaned, or killed).
	* '''Requires''': {{vbzcart/table\|rstk_rcd_line}}, {{vbzcart/table\|rstk_rcd}}, {{vbzcart/query\|qryRstkItms_active}}		* '''Requires''': {{vbzcart/table\|rstk_rcd_line}}, {{vbzcart/table\|rstk_rcd}}, {{vbzcart/query\|qryRstkItms_active}}
	* '''Used by''': ~~admin listing of needed items~~		* '''Used by''': {{vbzcart/query\|qryRstkItms_expected_byItem}}
	* '''Algorithm''': any positive quantities in '''<'''{{vbzcart/query\|qryRstkItms_active\|<all items in <active restock requests>>}} - <{{vbzcart\|table\|rstk_rcd_line\|items received}} on those item requests>'''>'''		* '''Algorithm''': any positive quantities in '''<'''{{vbzcart/query\|qryRstkItms_active\|<all items in <active restock requests>>}} - <{{vbzcart\|table\|rstk_rcd_line\|items received}} on those item requests>'''>'''
	* '''History''':		* '''History''':

Woozle: QtyOrd->QtyExp

2010-01-05T00:47:27Z

QtyOrd->QtyExp

← Older revision		Revision as of 00:47, 5 January 2010
Line 2:		Line 2:
	* '''Returns''': Restock items requested (ordered), not yet received, but possibly expected because the restock request hasn't been terminated (closed, orphaned, or killed).		* '''Returns''': Restock items requested (ordered), not yet received, but possibly expected because the restock request hasn't been terminated (closed, orphaned, or killed).
	* '''Requires''': {{vbzcart/table\|rstk_rcd_line}}, {{vbzcart/table\|rstk_rcd}}, {{vbzcart/query\|qryRstkItms_active}}		* '''Requires''': {{vbzcart/table\|rstk_rcd_line}}, {{vbzcart/table\|rstk_rcd}}, {{vbzcart/query\|qryRstkItms_active}}
	* '''Used by''':		* '''Used by''': admin listing of needed items
			* '''Algorithm''': any positive quantities in '''<'''{{vbzcart/query\|qryRstkItms_active\|<all items in <active restock requests>>}} - <{{vbzcart\|table\|rstk_rcd_line\|items received}} on those item requests>'''>'''
	* '''History''':		* '''History''':
	** '''2008-11-19''' Created for new restock process		** '''2008-11-19''' Created for new restock process
Line 8:		Line 9:
	** '''2008-12-04''' renamed: qryRstkItms_en_route → qryRstkItms_expected		** '''2008-12-04''' renamed: qryRstkItms_en_route → qryRstkItms_expected
	** '''2010-01-03''' fixed syntax: have to recalculate aggregate functions to use them in a HAVING clause (bleh)		** '''2010-01-03''' fixed syntax: have to recalculate aggregate functions to use them in a HAVING clause (bleh)
			** '''2010-01-04''' updated to use QtyExp instead of QtyOrd (field renamed/changed in {{vbzcart/query\|qryRstkItms_active}})
	==SQL==		==SQL==
	<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryRstkItms_expected AS		<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryRstkItms_expected AS
Line 13:		Line 15:
	rqi.ID_Restock,		rqi.ID_Restock,
	rqi.ID_Item,		rqi.ID_Item,
	SUM(rqi.~~QtyOrd~~) AS ~~QtyOrd~~,		SUM(rqi.QtyExp) AS QtyExp,
	SUM(IFNULL(rcl.QtyRecd,rcl.QtyFiled)) AS QtyRecd		SUM(IFNULL(rcl.QtyRecd,rcl.QtyFiled)) AS QtyRecd
	FROM		FROM
Line 20:		Line 22:
	LEFT JOIN qryRstkItms_active AS rqi ON (rc.ID_Restock=rqi.ID_Restock) AND (rcl.ID_Item=rqi.ID_Item)		LEFT JOIN qryRstkItms_active AS rqi ON (rc.ID_Restock=rqi.ID_Restock) AND (rcl.ID_Item=rqi.ID_Item)
	GROUP BY rqi.ID_Restock, rqi.ID_Item		GROUP BY rqi.ID_Restock, rqi.ID_Item
	HAVING (SUM(rqi.~~QtyOrd~~)-SUM(IFNULL(rcl.QtyRecd,rcl.QtyFiled)))>0;</mysql>		HAVING (SUM(rqi.QtyExp)-SUM(IFNULL(rcl.QtyRecd,rcl.QtyFiled)))>0;</mysql>
	<section end=sql />		<section end=sql />

Woozle: fixed SQL syntax, I hope

2010-01-03T14:53:04Z

fixed SQL syntax, I hope

← Older revision		Revision as of 14:53, 3 January 2010
Line 7:		Line 7:
	** '''2008-11-24''' '''QtyRecd''' falls back on '''QtyFiled''' if null		** '''2008-11-24''' '''QtyRecd''' falls back on '''QtyFiled''' if null
	** '''2008-12-04''' renamed: qryRstkItms_en_route → qryRstkItms_expected		** '''2008-12-04''' renamed: qryRstkItms_en_route → qryRstkItms_expected
			** '''2010-01-03''' fixed syntax: have to recalculate aggregate functions to use them in a HAVING clause (bleh)
	==SQL==		==SQL==
	<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryRstkItms_expected AS		<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryRstkItms_expected AS
Line 20:		Line 20:
	LEFT JOIN qryRstkItms_active AS rqi ON (rc.ID_Restock=rqi.ID_Restock) AND (rcl.ID_Item=rqi.ID_Item)		LEFT JOIN qryRstkItms_active AS rqi ON (rc.ID_Restock=rqi.ID_Restock) AND (rcl.ID_Item=rqi.ID_Item)
	GROUP BY rqi.ID_Restock, rqi.ID_Item		GROUP BY rqi.ID_Restock, rqi.ID_Item
	HAVING ~~IFNULL~~(QtyOrd,0)-IFNULL(QtyRecd,0);</mysql>		HAVING (SUM(rqi.QtyOrd)-SUM(IFNULL(rcl.QtyRecd,rcl.QtyFiled)))>0;</mysql>
	<section end=sql />		<section end=sql />

Woozle: /* About */ used by: ?

2008-12-05T01:43:40Z

About: used by: ?

← Older revision		Revision as of 01:43, 5 December 2008
Line 2:		Line 2:
	* '''Returns''': Restock items requested (ordered), not yet received, but possibly expected because the restock request hasn't been terminated (closed, orphaned, or killed).		* '''Returns''': Restock items requested (ordered), not yet received, but possibly expected because the restock request hasn't been terminated (closed, orphaned, or killed).
	* '''Requires''': {{vbzcart/table\|rstk_rcd_line}}, {{vbzcart/table\|rstk_rcd}}, {{vbzcart/query\|qryRstkItms_active}}		* '''Requires''': {{vbzcart/table\|rstk_rcd_line}}, {{vbzcart/table\|rstk_rcd}}, {{vbzcart/query\|qryRstkItms_active}}
			* '''Used by''':
	* '''History''':		* '''History''':
	** '''2008-11-19''' Created for new restock process		** '''2008-11-19''' Created for new restock process
	** '''2008-11-24''' '''QtyRecd''' falls back on '''QtyFiled''' if null		** '''2008-11-24''' '''QtyRecd''' falls back on '''QtyFiled''' if null
	** '''2008-12-04''' renamed: qryRstkItms_en_route → qryRstkItms_expected		** '''2008-12-04''' renamed: qryRstkItms_en_route → qryRstkItms_expected

	==SQL==		==SQL==
	<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryRstkItms_expected AS		<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryRstkItms_expected AS

Woozle: post-move cleanup & doc

2008-12-04T23:29:56Z

post-move cleanup & doc

← Older revision		Revision as of 23:29, 4 December 2008
Line 1:		Line 1:
	==~~Details~~==		==About==
	* '''Returns''': Restock items requested, not yet received, but possibly expected because the restock request hasn't been closed or ~~orphaned~~.		* '''Returns''': Restock items requested (ordered), not yet received, but possibly expected because the restock request hasn't been terminated (closed, orphaned, or killed).
	* '''Requires''': {{vbzcart/table\|rstk_rcd_line}}, {{vbzcart/table\|rstk_rcd}}, {{vbzcart/query\|qryRstkItms_active}}		* '''Requires''': {{vbzcart/table\|rstk_rcd_line}}, {{vbzcart/table\|rstk_rcd}}, {{vbzcart/query\|qryRstkItms_active}}
	* '''History''':		* '''History''':
	** '''2008-11-19''' Created for new restock process		** '''2008-11-19''' Created for new restock process
	** '''2008-11-24''' '''QtyRecd''' falls back on '''QtyFiled''' if null		** '''2008-11-24''' '''QtyRecd''' falls back on '''QtyFiled''' if null
			** '''2008-12-04''' renamed: qryRstkItms_en_route → qryRstkItms_expected
	==SQL==		==SQL==
	<section begin=sql /><mysql>CREATE OR REPLACE VIEW ~~qryRstkItms_en_route~~ AS		<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryRstkItms_expected AS
	SELECT		SELECT
	rqi.ID_Restock,		rqi.ID_Restock,

Woozle: VbzCart/queries/qryRstkItms en route moved to VbzCart/queries/qryRstkItms expected: reorganized terminology

2008-12-04T21:48:05Z

VbzCart/queries/qryRstkItms en route moved to VbzCart/queries/qryRstkItms expected: reorganized terminology

← Older revision	Revision as of 21:48, 4 December 2008
(No difference)

Woozle: New page: ==Details== * '''Returns''': Restock items requested, not yet received, but possibly expected because the restock request hasn't been closed or orphaned. * '''Requires''': {{vbzcart/table|...

2008-12-04T00:31:19Z

New page: ==Details== * '''Returns''': Restock items requested, not yet received, but possibly expected because the restock request hasn't been closed or orphaned. * '''Requires''': {{vbzcart/table|...

New page

==Details==
* '''Returns''': Restock items requested, not yet received, but possibly expected because the restock request hasn't been closed or orphaned.
* '''Requires''': {{vbzcart/table|rstk_rcd_line}}, {{vbzcart/table|rstk_rcd}}, {{vbzcart/query|qryRstkItms_active}}
* '''History''':
** '''2008-11-19''' Created for new restock process
** '''2008-11-24''' '''QtyRecd''' falls back on '''QtyFiled''' if null
==SQL==
<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryRstkItms_en_route AS
SELECT
rqi.ID_Restock,
rqi.ID_Item,
SUM(rqi.QtyOrd) AS QtyOrd,
SUM(IFNULL(rcl.QtyRecd,rcl.QtyFiled)) AS QtyRecd
FROM
(rstk_rcd_line AS rcl
LEFT JOIN rstk_rcd AS rc ON rcl.ID_RstkRcd=rc.ID)
LEFT JOIN qryRstkItms_active AS rqi ON (rc.ID_Restock=rqi.ID_Restock) AND (rcl.ID_Item=rqi.ID_Item)
GROUP BY rqi.ID_Restock, rqi.ID_Item
HAVING IFNULL(QtyOrd,0)-IFNULL(QtyRecd,0);</mysql>
<section end=sql />