Make sure you have indexes by DocId, CStatus and TTOD(DDate) in MYTABLE, then try this and see the result:

SYS(3054,12,"sqlTest")
   SELECT count(*);
          FROM MYTABLE;
          WHERE MYTABLE.docid = doctor.docid AND;
                MYTABLE.cstatus <>  "XXX"    AND;
                BETWEEN(TTOD(MYTABLE.ddate), tDateFrom, tDateTo)
   GROUP BY MYTABLE.caddress;
   INTO ARRAY laCount
MessageBox(sqlTest)

See what message will appear in MessageBox.
Also what type is MYTABLE.DDate and tDateFrom and tDateTo? All are dates or all are datetime?


In VFP 8 or later (for VFP7 not sure) you can write this in one statement:

 INSERT INTO newtable (Count) ;
        SELECT count(*);
              FROM MYTABLE;
              WHERE MYTABLE.cstatus <>  "XXX"    AND;
                    BETWEEN(TTOD(MYTABLE.ddate), tDateFrom, tDateTo)
              GROUP BY MyTable.DocId, MYTABLE.caddress

>Ok, I have a sql statement to count all the records that are unique per Doctor which looks similar to this...
>
>select doctor
>do while !eof()
> llcount = 0
>
> SELECT count(distinct(UPPER(ALLTRIM(MYTABLE.caddress)))) as llCount
> FROM MYTABLE WHERE MYTABLE.docid = doctor.docid
> AND MYTABLE.cstatus <> "XXX"
> AND BETWEEN(TTOD(MYTABLE.ddate), tDateFrom, tDateTo)
> INTO CURSOR curDocTot
>
> select newtable
> append blank
> replace newtable.count with llCount
>
> skip in doctor
>
>enddo
>
>Currently the MYTABLE contains more than 50K records. I also have an index in MYTABLE.ddate. My problem here is the speed of getting the NEWTABLE being populated, as I see it the bottle neck seems on the SELECT COUNT statment. Is there anything I can do to speed up that SELECT COUNT statment here? Please advice.
>TIA!