CLOSE DATABASES all
CLOSE TABLES all

*-- create simple test data
CREATE CURSOR t_erg		(cPeriod c(1), cSalesman c(1), nSales n(9,2), nPeriodPerc n(17,2) )
CREATE CURSOR t_data	(cPeriod c(1), cSalesman c(1), nSales n(9,2))

FOR lnRun = 1 TO 2
	INSERT INTO t_data VALUES ("1", "A", 0500)
	INSERT INTO t_data VALUES ("1", "B", 0700)
	INSERT INTO t_data VALUES ("1", "C", 0900)
	INSERT INTO t_data VALUES ("1", "D", 0300)
	INSERT INTO t_data VALUES ("1", "E", 0400)
	INSERT INTO t_data VALUES ("2", "A", 0100)
	INSERT INTO t_data VALUES ("2", "B", 0700)
	INSERT INTO t_data VALUES ("2", "C", 0900)
	INSERT INTO t_data VALUES ("2", "D", 1300)
	INSERT INTO t_data VALUES ("2", "E", 1400)
	INSERT INTO t_data VALUES ("3", "A", 0100)
	INSERT INTO t_data VALUES ("3", "B", 1700)
	INSERT INTO t_data VALUES ("3", "C", 0900)
	INSERT INTO t_data VALUES ("3", "D", 0800)
	INSERT INTO t_data VALUES ("3", "E", 0200)
	INSERT INTO t_data VALUES ("4", "A", 1100)
	INSERT INTO t_data VALUES ("4", "B", 1107)
	INSERT INTO t_data VALUES ("4", "C", 1110)
	INSERT INTO t_data VALUES ("4", "D", 0300)
	INSERT INTO t_data VALUES ("4", "E", 0400)
NEXT


* BUILD DATA DOMAIN, where cPeriod, cSalesman  is a pk !

SELECT cPeriod, cSalesman ;
		, SUM(nSales) as nSales ;
	FROM t_data ;
	GROUP BY 1,2 INTO CURSOR t_sum

* but, vfp DON'T SUPPORT THE SQL direct SOLUTION
	
SELECT t_sum2.*;
, 100*t_sum2.nSales/t_periods.sum_Period as nPeriodPerc ; 
FROM ;
	(SELECT * FROM t_sum ;
	  WHERE nSales IN (SELECT TOP 2 nSales FROM t_sum X WHERE cPeriod=t_sum.cPeriod ORDER BY 1 desc)) t_sum2 ;
inner join;
	(SELECT ;
			cPeriod ;
			; && example: in reality lots of calculated fields 
			, SUM(nSales) as sum_Period ; 
		FROM t_sum ;
		GROUP BY 1 ) t_periods;
	on t_sum2.cPeriod = t_periods.cPeriod;
		HAVING nPeriodPerc >= 24.5;
		order by 1,nSales desc

CLEAR

* indirect way ( implement a group counting with VFP with the HAVING scan behaviour ):

* Step 1
* BUILD DATA DOMAIN ORDERED !, where cPeriod, cSalesman  is a pk !

SELECT cPeriod, cSalesman ;
		, SUM(nSales) as nSales ;
		FROM t_data ;
		GROUP BY 1,2 ORDER BY 1,3 DESC ;
	INTO CURSOR t_sum
		
GROUP_ROW_INIT()
		
SELECT t_sum2.*;
, 100*t_sum2.nSales/t_periods.sum_Period as nPeriodPerc ; 
FROM ;
	(SELECT *	;
		FROM t_sum	HAVING GROUP_ROW(cPeriod) <=2 ) t_sum2 ;
inner join;
	(SELECT cPeriod ;
			, SUM(nSales) as sum_Period ; 
		FROM t_sum ;
		GROUP BY 1 ) t_periods;
	on t_sum2.cPeriod = t_periods.cPeriod;
		HAVING nPeriodPerc >= 24.5;
		ORDER BY 1,nSales desc;
	INTO CURSOR t_erg

GROUP_ROW_DESTROY()

USE IN t_sum

BROWSE

* this is an "ONE SELECT SOLUTION"

GROUP_ROW_INIT()

SELECT t_sum2.*;
, 100*t_sum2.nSales/t_periods.sum_Period as nPeriodPerc ; 
FROM FORCE ;
	(SELECT *	;
		FROM (SELECT cPeriod, cSalesman ;
				, SUM(nSales) as nSales ;
				FROM t_data ;
				GROUP BY 1,2;
				ORDER BY 1,3 DESC) t_sum;
	HAVING GROUP_ROW(cPeriod) <= 2 ) t_sum2 ;
INNER JOIN;
	(SELECT cPeriod ;
			, SUM(nSales) as sum_Period ; 
		FROM t_data ;
		GROUP BY 1 ) t_periods;
	ON t_periods.cPeriod = t_sum2.cPeriod ;
		HAVING nPeriodPerc >= 24.5;
	order by 1,nSales desc;
	INTO CURSOR t_erg1
	
GROUP_ROW_DESTROY()
BROWSE


*********************************************** GROUP COUNT FUNCTION **********

proc GROUP_ROW_INIT
	PUBLIC groupCount,groupKey
	groupCount	 = NULL
ENDPROC

proc GROUP_ROW_DESTROY
	RELEASE groupCount,groupKey
ENDPROC

PROC GROUP_ROW(_groupkey)

IF ISNULL(m.groupCount)
	groupKey	= NULL	&& this version doesn't support NULL key, it is simple to extend ...
	groupCount	= 0
	RETURN 0
ENDIF

IF M.groupkey == M._groupkey
	groupCount	= m.groupCount + 1
	RETURN m.groupCount
ENDIF

groupkey = M._groupkey
groupCount	 = 1
RETURN 1