>Hi all,
>
>I have mytable that data like this :
>
>mytable.dbf
>
>date amount categ
>2006-09-10 100000 A
>2006-08-10 200000 B
>2006-07-10 300000 C
>2006-06-10 400000 E
>2006-05-10 500000 F
>2006-04-10 100000 A
>2006-03-10 200000 B
>2006-02-10 300000 C
>2006-01-10 400000 E
>2005-12-10 500000 F
>2005-11-10 100000 A
>2005-10-10 200000 B
>2005-09-10 300000 C
>2005-08-10 400000 E
>2006-05-10 500000 F
>2006-09-10 100000 A
>2006-08-10 200000 B
>2006-07-10 300000 C
>2006-06-10 400000 E
>2006-05-10 500000 F
>
>to have newtable.dbf for last 12 months only,
>
>date amountsum categ datecount
>
>2006-09-10 200000 A 2
>2006-08-10 400000 B 2
>2006-07-10 600000 C 2
>2006-06-10 800000 E 2
>2006-05-10 1000000 F 2
>2006-04-10 100000 A 1
>2006-03-10 200000 B 1
>2006-02-10 300000 C 1
>2006-01-10 400000 E 1
>2005-12-10 500000 F 1
>2005-11-10 100000 A 1
>2005-10-10 200000 B 1
>
>I try this :
>
>select date,sum(amount) as amountsum,categ, count(date) as datecount
>from mytable group by date,amount
>
>Am i missing something ? please assist to fix my sql.
>
>TIA

ldCurrentDate     = DATE()
ldDateBefore12Mnt = GOMONTH(m.ldCurrentDate, -12)

select date,;
       sum(amount) as amountsum,;
       categ,;
       count(date) as datecount
from mytable;
group by date,categ;
WHERE mytable.Date BETWEEN m.ldDateBefore12Mnt AND m.ldCurrentDate;
INTO CURSOR cTest