>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

If I understand your question correctly, something like this should work for you:

SELECT B1.Code, B1.Date, B1.Price ;
  FROM Table2 B1 ;
    JOIN Table2 B2 ;
      ON B1.Code = B2.Code ;
      AND B1.Date <= B2.Date ;
  GROUP BY 1, 2, 3 ;
  HAVING CNT(*) <= 10 ;
  INTO CURSOR TenMostRecent

SELECT Code, Avg(Price) ;
  FROM TenMostRecent ;
  GROUP BY Code ;
  ORDER BY Code ;
  INTO CURSOR AvgByCode

Tamar


Hi Tamer
Thanks for your response. I tried but still did not get what is needed maybe because I did not explain clearly.

here is an extract of the data
Code ---- Date ---- Price
1010 -- 20061223 -- 63.25
1010 -- 20061224 -- 63.00
1010 -- 20061225 -- 63.75
1010 -- 20061226 -- 63.00
1010 -- 20061227 -- 64.50

1020 -- 20061223 -- 130.75
1020 -- 20061224 -- 130.00
1020 -- 20061225 -- 130.50
1020 -- 20061226 -- 132.00
1020 -- 20061227 -- 131.75

1030 -- 20061223 -- 83.00
1030 -- 20061224 -- 82.75
1030 -- 20061225 -- 83.00
1030 -- 20061226 -- 85.00
1030 -- 20061227 -- 88.50

1040 -- 20061223 -- 76.25
1040 -- 20061224 -- 74.75
1040 -- 20061225 -- 74.75
1040 -- 20061226 -- 74.25
1040 -- 20061227 -- 76.25

now say i want to get the top 3 dates in descending order in
each group(code) ie
1010 -- 20061225 -- 63.75 for the first group
1010 -- 20061226 -- 63.00
1010 -- 20061227 -- 64.50
and
1020 -- 20061225 -- 130.50 for the second group
1020 -- 20061226 -- 132.00
1020 -- 20061227 -- 131.75
etc..

I would grateful for any suggestions.

Rasheed