>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
>
>
>
>

Hi Thomas,

with VFP9 this is better and more readable

SELECT t_sum.*;
; && percentage as example: in reality oodles more of calculated fields 
, 100*t_sum.nSales/t_periods.sum_Period as nPeriodPerc ; 
FROM ;
	(SELECT ;
		cPeriod, cSalesman ;
		; && example: in reality lots of calculated fields 
		, SUM(nSales) as nSales ; 
		FROM t_data ;
		GROUP BY 1,2 ) t_sum;
inner join;
	(SELECT ;
			cPeriod ;
			; && example: in reality lots of calculated fields 
			, SUM(nSales) as sum_Period ; 
		FROM t_data ;
		GROUP BY 1 ) t_periods;
	on t_sum.cPeriod = t_periods.cPeriod;
			; && but not all periods will have 2 rows!
			; && only those above a threshold...
			HAVING nPeriodPerc >= 24.5;
			order by nSales desc ;
INTO CURSOR t_erg

with VFP8 or before, split it in 2 steps

Another way is ( but this can to be slower in smart SQL backend ):

* put all info in max dimensions grouping
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

* uses max grouping for build derived groups 
* ( unfortunately SQL syntax doesn't support this concept :((( )
SELECT ;
    cPeriod ;
    ; && example: in reality lots of calculated fields 
    SUM(nSales) as sum_Period ; 
    FROM t_sum ;
    GROUP BY 1 into cursor  t_periods

SELECT t_sum.*;
; && percentage as example: in reality oodles more of calculated fields 
, 100*t_sum.nSales/t_periods.sum_Period as nPeriodPerc ; 
FROM ;
...
inner ...

the SCAN have an advantage:
you can compensate the percent round error in the loop,
with SELECT you have to compute another grouping and do the update distribution.