>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