Hi Fabio,

Thank for informations and help.

I wish simple information initially.
I would like the number of days when PAUSEID # 0 AND cpauseid2 # 0 for April 2005 for each employe.

 SELECT H.iemployeeid,;
    MAX( P1.totalp1 ),;
    MAX( P2.totalp2 );
    FROM Horaire H,;
    (SELECT HP1.iemployeeid , SUM( IIF( ISNULL(HP1.iemployeeid ) , 1 , 0 )) totalp1;
    FROM Horaire HP1 ;
    LEFT JOIN PAUSE P ON  HP1.cpauseid = P.iid ;
    WHERE HP1.ddate BETWEEN D_debut AND d_fin ;
    GROUP BY 1) P1 ,;
    (SELECT HP2.iemployeeid , SUM( IIF( ISNULL(HP2.iemployeeid) , 1 , 0 )) totalp2;
    FROM Horaire HP2 ;
    LEFT JOIN PAUSE P ON  HP2.cpauseid2 = P.iid ;
    WHERE HP2.ddate BETWEEN D_debut AND d_fin ;
    GROUP BY 1) P2 ;
    WHERE  H.ddate BETWEEN {^2005/04/01} AND {^2005/04/30};
    AND H.iemployeeid = P1.iemployeeid ;
    AND H.iemployeeid = P2.iemployeeid ;
    GROUP BY 1 INTO CURSOR TEMP

I put my data on
WWW.LESBOULEAUX.COM\data1.zip

bernhart


>Informations are insufficient.
>try
>

>SELECT Horaire.iemployeeid, sum( Horaire.n100)+sum(Horaire.n120),;
>  sum( Horaire.n100_2)+sum( Horaire.n120_2),PADR(MAX(Pause.cnom),20),;
>  PADR(MAX(Pause_a.cnom),20);
> FROM amline!Horaire ;
>    LEFT OUTER JOIN  amline!Pause ON  Pause.iid = Horaire.cpauseid2 ;
>    LEFT OUTER JOIN amline!Pause Pause_a ON  Horaire.cpauseid = Pause_a.iid;
> WHERE  Horaire.ddate BETWEEN {^2005/04/01} and {^2005/04/30};
> GROUP BY Horaire.iemployeeid
>

>
>>Fabio,
>>
>>thank for your precious help.
>>
>>

>>SELECT Horaire.iemployeeid, sum( Horaire.n100+ Horaire.n120),;
>>  sum( Horaire.n100_2+ Horaire.n120_2), MAX(PADR( Pause.cnom,20)),;
>>  MAX(PADR( Pause_a.cnom,20));
>> FROM ;
>>     amline!Pause ;
>>    LEFT OUTER JOIN amline!Horaire ;
>>   ON  Pause.iid = Horaire.cpauseid2 ;
>>    LEFT OUTER JOIN amline!Pause Pause_a ;
>>   ON  Horaire.cpauseid = Pause_a.iid;
>> WHERE  Horaire.ddate BETWEEN {^2005/04/01} and {^2005/04/30};
>> GROUP BY Horaire.iemployeeid
>>>/PRE>
>>
>>Give me a bad result !
>>
>>I think that union is perhaps essential ?
>>
>>
>>Horaire.dbf n100   ------------------------> Pause.dbf  ( cpauseid) first pause
>>            n100_2 ------------------------> pause.dbf  ( cpauseid2 ) second pause
>>
>>
>>How can i have sum( n100 ) + sum( n100_2 ) per employeeid ?
>>
>>>>I try it...
>>>>
>>>>i must verify...
>>>>
>>>><PRE>
>>>>
>>>>  SELECT H.iemployeeid  ,;
>>>>    COUNT(*) totaljo ,;
>>>>    SUM( IIF( INLIST( .F. , E.Lassimilinami , P.Iassimilid , P2.Iassimilid ) , H.n100 + H.n100_2 + H.n120 + H.n120_2 , 0.00 )) totHoNAs ,;
>>>>    SUM( IIF( E.Lassimilinami = .T. AND ( P.Iassimilid = .T. OR P2.Iassimilid = .T. ) , H.n100 + H.n100_2 + H.n120 + H.n120_2 , 0.00 )) totHoAs ;
>>>>    FROM ;
>>>THIS DOUBLE INNER IS USELESS
>>>
>>>    Horaire H INNER JOIN PAUSE P ON  H.cpauseid = P.iid ;
>>>    INNER JOIN PAUSE P2 ON  H.cpauseid = P2.iid ;
>>>
>>>YOU MAKE A TYPO ERROR
>>>YOU THINK THIS
>>>    Horaire H INNER JOIN PAUSE P ON  H.cpauseid = P.iid ;
>>>    INNER JOIN PAUSE P2 ON  H.cpauseid2 = P2.iid ;
>>>
>>>BUT THIS CANNOT WORK BECAUSE FOR WORK H.cpauseid AND H.cpauseid2 shoukd match
>>>
>>>try this:
>>>    Horaire H LEFT JOIN PAUSE P ON  H.cpauseid = P.iid ;
>>>    LEFT JOIN PAUSE P2 ON  H.cpauseid2 = P2.iid ;
>>>and overridde the Iassimilid nulls
>>>( remember the rule NULL OR .T. is .T. and NULL AND .F. is .F.)
>>>