CLOSE all
SET DELETED ON

SET TALK OFF
SET ECHO OFF
*Moved next two lines here, because we need to open table2 just once, not inside the scan loop
USE ttest
SET FILTER  TO team=0
DELETE ALL
PACK 

m.aa= (thisform.text1.value)
 SELECT 0 && Always use 0 instead of explicit number, 0 will open the next available area
    USE hato alias hato && I would use better names for my tables even for temporary
* Step 1 - calculate percentages
* Each team record count per each area
select areacode, team, count(*) as TeamCount from ttest ;
group by areacode, team into cursor curTeamCount

* Total number of records per area from every team
TotalFetchNumber=m.aa
select areacode, count(*) as AreaCount from ttest ;
group by areacode into cursor curAreaCount
#define thisform.text1.value && We need to have 150 records per each area
#define Multiplier 4
* Percentage of each team record count per area
select curTeamCount.areacode, team, TeamCount, ;
         AreaCount, ;
        TeamCount/AreaCount*100 as Percentage, ;
        floor((TeamCount/AreaCount) *  TotalFetchNumber * Multiplier) as NumToFetch ;
         from curTeamCount inner join curAreaCount on ;
        curTeamCount.areaCode = curAreaCount.areaCode into cursor curPercentage ;
        order by 1, 2 && AreaCode, Team

* Close cursors, which are no longer needed
use in curAreaCount
use in curTeamCount

local lnNumberToExtract, lnAreaCode, lnTeam && Assuming AreaCode and Team are integer PKs

select curPercentage
scan
     
   
   lnAreaCode = curPercentage.AreaCode
lnTeam = curPercentage.Team

lnNumberToExtract = curPercentage.NumToFetch
if m.lnNumberToExtract<curPercentage.TeamCount
   ** Now use code from Sergey Berezniker / Al Doman
    SELECT TOP lnNumberToExtract *, RECNO() AS rn,  ; && i get the error meesage .
   ROUND(RAND()*10000000000,0) AS ord ;
    FROM ttest ;
    WHERE AreaCode = m.lnAreaCode and Team = m.lnTeam ;
    ORDER BY ord ;
    INTO CURSOR crsRand
else && we exceed original number of records, just select all records for that team
   select *, recno() as rn from ttest WHERE AreaCode = m.lnAreaCode and Team = m.lnTeam into cursor crsRand NOFILTER
endif