>Fabio,
>
>a big thank !!!!
>
>This query give goods results but it is possible to optimize the whereClaus ?
>

* ADD THIS INDEX TO Horaire
INDEX ON ddate FOR NOT DELETED() TAG DATE

* THIS IS 10x FASTER NOW
SELECT ;
	iemployeeid;
,	totaljop1;
,	totaljop2;
,	totaljop1 + totaljop2 total;
,	LEFT( NVL( F.cnom , '' ), 20 ) cnomf;
,	F.Nordre;
,	E.cnosecretariat;
,	E.cparaphe;
,	E.iFonctionId;
,	E.nextrapolhoraire;
,	E.nextrapolhoraire_n;
,	totHoNAs ;
,	totHoAs ;
	FROM;
 FORCE (SELECT ;
    H.iemployeeid,;
    COUNT(P1.iid) totaljop1,;
    COUNT(P2.iid) totaljop2,;
    SUM( IIF( NOT (E.Lassimilinami AND P1.Iassimilid) , H.n100 + H.n100_2 + H.n120 + H.n120_2 , 0.00 )) totHoNAs ,;
    SUM( IIF(      E.Lassimilinami AND P1.Iassimilid  , H.n100 + H.n100_2 + H.n120 + H.n120_2 , 0.00 )) totHoAs ;
    FROM FORCE Horaire H;
        JOIN Employee E ON  E.iid = H.iemployeeid ;
    			AND H.ddate BETWEEN m.D_debut AND m.d_fin ; && DON'T MOVE THIS !
    			AND (H.cpauseid2 <> 0 OR  H.cpauseid <> 0 ); && DON'T MOVE THIS !
    LEFT JOIN PAUSE P1 ON P1.iid = H.cpauseid;
    LEFT JOIN PAUSE P2 ON P2.iid = H.cpauseid2;
    GROUP BY 1) GRP;
    	JOIN Employee E ON  E.iid = GRP.iemployeeid;
    	LEFT OUTER JOIN Fonction F ON  F.iid = E.iFonctionId; && SURE LEFT ? 
    INTO CURSOR Temp2
 NEXT

>bernhart
>
>

> SELECT ;
>    H.iemployeeid,;
>    COUNT(P1.iid) totaljop1,;
>    COUNT(P2.iid) totaljop2,;
>    COUNT(P2.iid) + COUNT(P1.iid) total,;
>    MAX( PADR( NVL( F.cnom , '' ), 20 ) ) cnomf,;
>    MAX( F.Nordre ) nOrdreF,;
>    MAX( E.cnosecretariat ) cnosecret,;
>    MAX( E.cparaphe ) cparaphe,;
>    MAX( E.iFonctionId ) iFoncId,;
>    MAX( E.nextrapolhoraire ) nextrAss ,;
>    MAX( E.nextrapolhoraire_n ) nextrNass ,;
>    SUM( IIF( E.Lassimilinami = .F. OR P1.Iassimilid = .F. , H.n100 + H.n100_2 + H.n120 + H.n120_2 , 0.00 )) totHoNAs ,;
>    SUM( IIF( E.Lassimilinami = .T. AND P1.Iassimilid = .T. , H.n100 + H.n100_2 + H.n120 + H.n120_2 , 0.00 )) totHoAs ;
>    FROM Horaire H;
>    LEFT JOIN PAUSE P1 ON P1.iid = H.cpauseid;
>    LEFT JOIN PAUSE P2 ON P2.iid = H.cpauseid2;
>    INNER JOIN Employee E ON  E.iid = H.iemployeeid;
>    LEFT OUTER JOIN Fonction F ON  F.iid = E.iFonctionId;
>    WHERE H.ddate BETWEEN m.D_debut  AND m.d_fin AND;
>    ( ( H.cpauseid <> 0 AND H.cpauseid2 <> 0 ) OR ;
>    ( H.cpauseid2 <> 0 AND H.cpauseid = 0 ) OR;
>    ( H.cpauseid2 = 0 AND H.cpauseid <> 0 ) );
>    GROUP BY 1 ;
>    INTO CURSOR Temp2
>
>

>
>
>>

>>D_debut = {^2005/04/01}
>>d_fin   = {^2005/04/30}
>>
>>SELECT ;
>> 	H.iemployeeid;
>>, 	COUNT(p.iid) totalp1;
>>, 	COUNT(p2.iid) totalp2;
>>	FROM Horaire H;
>>	LEFT JOIN PAUSE P ON P.iid   = H.cpauseid;
>>	LEFT JOIN PAUSE P2 ON P2.iid = H.cpauseid2;
>>    WHERE (H.cpauseid <> 0 OR H.cpauseid <> 0);
>>       AND H.ddate BETWEEN m.D_debut  AND m.d_fin;
>>    GROUP BY 1 ;
>>INTO CURSOR TEMP
>>BROWSE
>>
>>* WITH REFERENTIAL Integrity THIS IS LIKE COUNT cpauseid#0
>>* DAY(NULL) IS A FAST CAST
>>SELECT ;
>> 	H.iemployeeid;
>>, 	COUNT(EVL(H.cpauseid,DAY(NULL))) totalp1;
>>, 	COUNT(EVL(H.cpauseid2,DAY(NULL))) totalp2;
>>	FROM Horaire H;
>>    WHERE (H.cpauseid <> 0 OR H.cpauseid <> 0);
>>     AND H.ddate BETWEEN m.D_debut  AND m.d_fin;
>>    GROUP BY 1 ;
>>INTO CURSOR TEMP2
>>BROWSE
>>RETURN
>>

>>Point 1:
>>If you change cpauseid=0 to null ( same for cpauseid2 )
>>the sql commands are more simple and portable.
>>
>>Another point:
>>you define
>>

>>INDEX ON DELETED() TAG DELREC
>>

>>This is not good.
>>
>>On VFP9 you can use
>>

>>INDEX ON DELETED() TAG DELREC BINARY && 4X faster and bloat insensible
>>

>>but this is not the best solution.
>>
>>Best is:
>>

>>* erase DELREC
>>* change
>>INDEX ON iid FOR NOT DELETED() TAG primary
>>

>>With this VFP9 use primary for to exclude the deleted records,
>>and when you use a PK match, VFP optimize the match and the deleted() test
>>with a single index!
>>
>>Best if you add FOR NOT DELETED() on all the tags.
>>
>>Fabio