Hi,

I am currently writing some routines in the classes of a co-worker off for summer holiday.
My co-worker iften states his dislike for using scan-endscan when a pure SQL-alternative exists, whereas I use what I believe is best for the current problem and my current mood <g>.

I have rewritten the basic problem in a more typical period/salesman/sales structure below. I think the scan-endscan is "fitting" here, but maybe you see a nice "pure SQL" way which is more succinct. As an element of friendly banter and ribbing in the office also exists, I dont want to overlook something obvious<g>.

tia

thomas

*-- create simple test data
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", 0700)
	INSERT INTO t_data VALUES ("4", "C", 0900)
	INSERT INTO t_data VALUES ("4", "D", 2300)
	INSERT INTO t_data VALUES ("4", "E", 0400)
next

*-- the logic looks simple when the field lists are short,
*-- but actual code goes across a few pages!
*-- any way to formulate this better/shorter in pure SQL ?
SELECT ;
		cPeriod ;
		; && example: in reality lots of calculated fields 
		, SUM(nSales) as sum_Period ; 
	FROM t_data ;
	GROUP BY 1 ;
	INTO CURSOR t_periods

SELECT ;
		cPeriod, cSalesman ;
		; && example: in reality lots of calculated fields 
		, SUM(nSales) as nSales ; 
	FROM t_data ;
	GROUP BY 1,2 ;
	INTO CURSOR t_sum

CREATE CURSOR t_erg	(cPeriod c(1), cSalesman c(1), nSales n(9,2), nPeriodPerc n(6,2) )
*-- next line unneccessary if I reorder selects above, 
*-- but for me this order of creating cursors seems more "fitting"
SELECT t_Periods
SCAN
	INSERT INTO t_erg ;
		SELECT all top 2 *;
			; && percentage as example: in reality oodles more of calculated fields 
			, 100*nSales/t_periods.sum_Period as nPeriodPerc ; 
			FROM t_sum ;
			WHERE t_sum.cPeriod = t_periods.cPeriod ;
			order by nSales desc ;
			; && but not all periods will have 2 rows!
			; && only those above a threshold...
			having nPeriodPerc >= 24.5
Endscan