>i have a table with fields date (date type) and tfamt (logical).
>
>i want to select from this table all records with tfamt = .T. that falls in 3 consecutive days in a week..
>
>say,
>
>
>12/01/2003 .T.
>12/02/2003 .T.
>12/03/2003 .T.
>12/04/2003 .F.
>12/05/2003 .F.
>12/06/2003 .F.
>12/07/2003 .F.
>12/08/2003 .T.
>12/09/2003 .T.
>12/10/2003 .F.
>12/11/2003 .F.
>12/12/2003 .T.
>
>
>i should be able to select dates 12/01-12/03...... with 3 consecutive days in a week...
>
>any help?
The following appears to work for me:
Set Safety Off
Rand(-1)
Create Table Test (dTest D, lFlag L)
For iTestRec = 1 To 50
Insert Into Test Values (Date() + iTestRec, Rand() > 0.5 )
Next iTestRec
Locate
Browse Last NoWait
Select dTest ;
From Test ;
Into Cursor curResults ;
Where lFlag ;
And dTest + 1 In (Select dTest From Test Where lFlag) ;
And dTest + 2 In (Select dTest From Test Where lFlag)
Browse Last NoWait
Return .T.
Also you have not specified what results should be returned where the table has 4 (or more) consecutive dates all with .T. The code above will return the first-date, first-date + 1, and so on.
How many records are being processed and how often will you be running the query. I ask because indexes on fields with a limited set of values (Boolean in particular) will not always yield the best possible performance results - see many discussion about the fallacy of always indexing on Deleted().
In VFP 9, there is a new index type called binary, this is optimized for the Deleted flag, and hopefully will be usable with any Boolean fields.
Another option is to do away with the flag altogether and use two tables one for those dates that are .T. and the other for those that are not.
censored.