Hi Fabio
This time i am answering in the end of the message to be closer to the code :-)

>>Fabio
>>1) I first got 650 records from a 257000 records, which took between 3.5 to 4 seconds. Now i also narrowed it with an additional filter which cannot be optimized, and it still gives me 130 records in 4 seconds. For me this is more than adequate anyway
>>
>>2) It's weird why it doesn't group them in the same row when possible. Here is my SELECT, you have to sit down before you read it :-)

>>  SELECT Shaa, MAX(IIF(DOW(Date)=1,Patient.Shem+Sug+left(NVL(SugTip.SugTipul,"  "),2) ,SPACE(28))) AS Sunday, ;
>>   MAX( IIF(DOW(Date)=2, Patient.Shem+Sug+left(NVL(SugTip.SugTipul,"  "),2) ,SPACE(28))) AS Monday,;
>>   MAX( IIF(DOW(Date)=3, Patient.Shem+Sug+left(NVL(SugTip.SugTipul,"  "),2) ,SPACE(28))) AS Tuesday,;
>>   MAX( IIF(DOW(Date)=4, Patient.Shem+Sug+left(NVL(SugTip.SugTipul,"  "),2) ,SPACE(28))) AS Wednesday ;
>>FROM Yoman T ;
>>JOIN ( SELECT T.Oper, COUNT(*) rowInGroup ;
>>				FROM Yoman T ;
>>				LEFT JOIN Yoman TB ;
>>				ON TB.Oper <= T.Oper ;
>>				AND TB.Shaa=T.Shaa ;
>>				AND DOW(TB.Date)=DOW(T.Date) ;
>>				WHERE T.Date between lStartDate AND lStartDate+3 ;
>>				   AND INLIST( RofeCode, T.M1, T.M2, T.M3, T.M4 ) ;
>>				   AND !INLIST( T.Bit, "ì", "ð", "á" ) ;
>>				GROUP BY 1) GG ON T.Oper = GG.Oper ;
>>LEFT JOIN Patient ON T.PCode=Patient.Code ;
>>FULL JOIN Sugtip ON T.Tipul=SugTip.Code ;
>>WHERE T.Date between lStartDate AND lStartDate+3 ;
>>	AND INLIST( RofeCode, T.M1, T.M2, T.M3, T.M4 ) ;
>>	AND !INLIST( T.Bit, "ì", "ð", "á" ) ;	
>>GROUP BY Shaa , rowInGroup INTO TABLE mytesttable

>>
>>As you guessed, Shaa means time, and instead of just the patient name, i take also some other stuff i use in the report for conditional formatting.
>>
>>Jaime
>>
>>
>
>the previous is wrong, use this
>( I have added some comment )
>

>SELECT Shaa	;
>,  MAX( IIF(DOW(Date)=1, Patient.Shem+Sug+left(NVL(SugTip.SugTipul,"  "),2) ,SPACE(28))) AS Sunday, ; && put the comma at end is a bad syntax
>   MAX( IIF(DOW(Date)=2, Patient.Shem+Sug+left(NVL(SugTip.SugTipul,"  "),2) ,SPACE(28))) AS Monday,;
>   MAX( IIF(DOW(Date)=3, Patient.Shem+Sug+left(NVL(SugTip.SugTipul,"  "),2) ,SPACE(28))) AS Tuesday,;
>   MAX( IIF(DOW(Date)=4, Patient.Shem+Sug+left(NVL(SugTip.SugTipul,"  "),2) ,SPACE(28))) AS Wednesday ;
>FROM ( SELECT T.Oper, COUNT(*) rowInGroup ;
>	FROM	 Yoman T ;
>	    JOIN Yoman TB ;
>		ON TB.Oper <= T.Oper ;
>		AND TB.Shaa==T.Shaa ;
>		AND DOW(TB.Date)=DOW(T.Date) ;
>	WHERE	T.Date between m.lStartDate AND m.lStartDate+3 ;  && put the m.,
>	   AND TB.Date between m.lStartDate AND m.lStartDate+3 ;
>	   AND m.RofeCode IN (  T.M1,  T.M2,  T.M3,  T.M4 ) ; && RofeCode is a variable ?
>	   AND m.RofeCode IN ( TB.M1, TB.M2, TB.M3, TB.M4 ) ;
>	   AND  T.Bit NOT IN ("ì", "ð", "á" ) ;
>	   AND TB.Bit NOT IN ("ì", "ð", "á" ) ;
>	GROUP BY 1) GG;
>JOIN Yoman T ON T.Oper = GG.Oper;
>LEFT JOIN Patient ON T.PCode=Patient.Code ; && LEFT JOIN IS NOT CORRECT WITH THE IIF(,Patient.Shem+Sug+ expression
>FULL JOIN Sugtip ON T.Tipul=SugTip.Code ;	&& FULL JOIN ??? sure ?
>GROUP BY Shaa , rowInGroup INTO TABLE mytesttable
>

1) You got it right, m.RofeCode is a variable
2) Patient.Shem holds the name of the patient. How do you expect me to get it? Full join? ( since some records in Yoman don't have pcode )
3) I use full join with SugTip by instinct, because i got hit a while ago in some other query with it...In fact it's like with patient and pcode, some records in yoman have and some records don't ( that's why i used nvl and i think that's why you used Max(Patient) in your original suggestion )

Jaime