SET TALK OFF
SET ECHO OFF
* Step 1 - calculate percentages
* Each team record count per each area
select areacode, team, count(*) as TeamCount from table1 ;
group by areacode, team into cursor curTeamCount

* Total number of records per area from every team
select areacode, count(*) as AreaCount from table1 ;
group by areacode into cursor curAreaCount

* Here I'm combining steps 1 & 2
#define TotalFetchNumber 150 && 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

** I do not perform checks, if Number To fetch is greater than number of records -> to do list

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

*? 'areacode', 'Team', 'Count', 'Percentaje', 'Number of Records to Fetch'
local lnNumberToExtract, lnAreaCode, lnTeam && Assuming AreaCode and Team are integer PKs

select curPercentage
scan
     *? AreaCode, Team, TeamCount, Percentage, NumToFetch && Just for monitoring, this info would be nice to put in some text file

   ** I guess, we can use curPercentage fields directly in the following query, 
   ** but I am introducing 3 local vars for maintanability (did I spell that word right? :)

   lnAreaCode = curPercentage.AreaCode
lnTeam = curPercentage.Team

lnNumberToExtract = curPercentage.NumToFetch
if m.lnNumberToExtract<curPercentage.TeamCount    
   ** Now use code from Sergey Berezniker (Al Doman) - may be not efficient, but quite elegant
    SELECT TOP lnNumberToExtract *, RECNO() AS rn,  ;
   ROUND(RAND()*10000000000,0) AS ord ;
    FROM table1 ;
    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 * from table1 WHERE AreaCode = m.lnAreaCode and Team = m.lnTeam into cursor crsRand NOFILTER
endif

    
    
    ** Assuming that NewTable is our new table and it is already created and opened
    SELECT 2
    USE  table2
     append from dbf("crsRand")
   delete from table1 where recno() in (select RecNum from crsRand)


    
    

    use in crsRand && we do not need our crsRand anymore

    ** Let's note, that I do not check, if we have the exact number of records or more (the probability is very low) - may need to add this check later

    ** Ok, seems like everything is done according to the plan
 

       endscan
REPORT FORM rpt1 preview
use in curPercentage