LEFT OUTER JOIN Fonction F ON F.iid = E.iFonctionId; && SURE LEFT ?

yes because all employee have'nt a fonction.

Thank you fabio

 * THIS IS 10x FASTER NOW
  SELECT ;
    iemployeeid;
    ,	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,;
    SUM( IIF( NOT ISNULL(P1.iid), 1, 0) + ;
    IIF( NOT ISNULL(P2.iid) AND ISNULL(P1.iid), 1, 0)) AS TOTAL,;
    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

>>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