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
>>Hi Fabio
>>Thank you for all the time you tool helping me with this, i really appreciate it.
>
>This is a general problem, and the solution is also useful to me
>
>>1) I think you misunderstood the time issue: it took 4 seconds to run your code **with a 250,000 records** table, which is in this case, very very nice.
>
>It doesn't convince me.
>How many are the records of the result?
>
>>2) What i meant is that when running this in my real table, the results are correct, but they are not grouped correctly by time, like in:
>>CTime Sunday Monday Tuesday ...
>>10:00 Fabio
>>10:00 Fabrizio <-- why not in the upper row?
>>10:15 Jaime Fabio
>>
>
>Post the filtered SELECT-SQL, something doesn't certainly work
>
>>What i meant is that for more or less 1% of cases i get like in the 10:15 above ( 2 or more in the same row ), generally they are splitted.
>>
>>As i also mentioned, this is not a problem. Just woke up, and while drinking a huge ristretto, am writing a SCAN...ENDSCAN that will tighten those rows a little more :-)
>>
>>Jaime
>>
Why do programs stop working correctly as soon as you leave the Fox?