>>>Fabio
>>>Why is it you need it? I have an OperationID field which is the primary key for the table, but i don't think it's relevant in this case.
>>>Here, the user will enter the date for Sunday ( let's call it SunDate ) and i just extract all records where BETWEEN( CDate, SunDate, SunDate+3 ) ordered by CTime.
>>>Jaime
>>
>>
>>SELECT ;
>> CTime ;
>>, MAX(IIF(DateW=1,Patient,SPACE(30))) AS Sunday ;
>>, MAX(IIF(DateW=2,Patient,SPACE(30))) AS Monday ;
>>, MAX(IIF(DateW=3,Patient,SPACE(30))) AS Tuesday ;
>>, MAX(IIF(DateW=4,Patient,SPACE(30))) AS Wednesday ;
>>, MAX(IIF(DateW=5,Patient,SPACE(30))) AS Thursday ;
>>, MAX(IIF(DateW=6,Patient,SPACE(30))) AS Friday ;
>>, MAX(IIF(DateW=7,Patient,SPACE(30))) AS Saturday ;
>> FROM (SELECT MAX(T.CTime) CTime ;
>> , MAX(DOW(T.CDate)) DateW ;
>> , MAX(T.Patient) Patient ;
>> , COUNT(*) rowInGroup;
>> FROM theTable T ;
>> LEFT JOIN theTable RN ;
>> ON RN.OperationID <= T.OperationID ;
>> AND RN.CTime=T.CTime ;
>> AND DOW(RN.CDate)=DOW(T.CDate);
>> GROUP BY T.OperationID) GG;
>> GROUP BY CTime,rowInGroup
>>
>>if it is slow, a variation exists
>
>Hey Fabio
>1) It's extremely slow ( my lamborgini computer is stucked on it already for 5 minutes and still didn't finish :-) ) even if i deleted the code for thursday friday and saturday
These are too much complex things for VFP,
only an advanced engine (at least SQL Server)
it is able to use the indexes to optimize this query ( or that in the other message),
but queries exist where also SQL Server surrenders him
and use a slow filter on a products Cartesian.
>2) i don't understand this join
FROM theTable T ;
> LEFT JOIN theTable RN ;
> ON RN.OperationID <= T.OperationID ;
> AND RN.CTime=T.CTime ;
> AND DOW(RN.CDate)=DOW(T.CDate)
This build a row sequential number for avery T.CTime,DOW(T.CDate) couple,
then : T.CTime,DOW(T.CDate),rowInGroup become a candidate key.
At this point, GROUP BY CTime,rowInGroup
it merge the lines with the same CTime,rowInGroup,
and the operation of transposition of DOW(T.CDate)
put every candidate item into a specific cell of the result.
>Thanks
>Jaime