Thank sergey,

But it give me a bad result...

If H.cpauseid <> 0 AND H.cpauseid2 <> 0 =========> 1 Day
If H.cpauseid <> 0 AND H.cpauseid2 = 0 =========> 1 Day
If H.cpauseid2 <> 0 AND H.cpauseid1 = 0 =========> 1 Day
If H.cpauseid2 = 0 AND H.cpauseid1 <> 0 =========> 1 Day
If H.cpauseid2 =0 AND H.cpauseid1 = 0 =========> 0 Day


bernhart




>Try

    WHERE H.ddate BETWEEN m.D_debut  AND m.d_fin ;
>      AND NOT ( H.cpauseid = 0 AND H.cpauseid2 = 0 )
>* or
>    WHERE H.ddate BETWEEN m.D_debut  AND m.d_fin ;
>      AND ( H.cpauseid <> 0 OR H.cpauseid2 <> 0 )
>
>

>
>>Fabio,
>>
>>a big thank !!!!
>>
>>This query give goods results but it is possible to optimize the whereClaus ?
>>
>>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
>>
>>

>>
>>