Hi All;

I have a table with a few 100,000s records and it contains over 80 different company codes. evryday I need to calculate average price for the last 10 days for each company. The code i use is something like this.

use table1 in 0 alias A
use table2 in 0 alias B

select A
go top
scan
   cCode = A.Code
   select top 10 B.Code, B.Date, B.nPrice ;
   from Table2 B ;
   where B.Code = cCode ;
   order by b.Date Desc ;
   into cursor cTmp01 ;

   then i calculate the average like this;
   select a.code, avg(a.nPrice) as nAvgPrc ;
   from cTmp01 A ;
   order by a.code ;
   group by a.code ;
   into cursor cTmp02
endscan

As you can see i have to loop 80 times or so to achieve this.

Is there is a way where i can select the top 10 for each code in one select before i start the scan.
also any suggestion to improve in this code.

Your help is much appreciated.

Regards

Rasheed