THank sergey,

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;
NOT ( H.cpauseid = 0 AND H.cpauseid2 = 0 );
GROUP BY 1 ;
INTO CURSOR Temp2

I should like for that employee 24 Days but the query give me 25 days ( query.total )
because the 8 april er is 2 pauses.

24 days is the good result ....

bernhart


>Nice picture but it doesn't expalin what's wrong with query. Can you describe what conditions you want to apply to this query?
>BTW, where H.cpauseid1 came from? I don't see it in your query?
>
>>
>>www.lesbouleaux.com\UT1.jpg
>>
>>I should like 24 day of work ......
>>But it give me 25 jours ( days )
>>
>>bernhart
>>
>>>The WHERE clause I posted is equivalent of the WHERE clause from your query. What result do you want/expect?
>>>
>>>>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
>>>>>>
>>>>>>

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

>>>>>>
>>>>>>