>Hi,
>
>
>I should like group all the requery on H.iemployeeid
>
>

>
>  D_debut = {^2005/04/01}
>  d_fin = {^2005/04/30}
>
>  SELECT H.iemployeeid  ,;
>    COUNT(*) totaljo ,;
>    SUM( IIF( E.Lassimilinami = .F. OR P.Iassimilid = .F. , H.n100 + H.n100_2 + H.n120 + H.n120_2 , 0.00 )) totHoNAs ,;
>    SUM( IIF( E.Lassimilinami = .T. AND P.Iassimilid = .T. , H.n100 + H.n100_2 + H.n120 + H.n120_2 , 0.00 )) totHoAs ;
>    FROM ;
>    Horaire H INNER JOIN PAUSE P ON  H.cpauseid = P.iid AND H.ddate BETWEEN D_debut AND d_fin ;
>    INNER JOIN Employee E ON  E.iid = H.iemployeeid;
>    GROUP BY 1;
>    UNION;
>    SELECT H.iemployeeid  ,;
>    COUNT(*) totaljo ,;
>    SUM( IIF( E.Lassimilinami = .F. OR P.Iassimilid = .F. , H.n100 + H.n100_2 + H.n120 + H.n120_2 , 0.00 )) totHoNAs ,;
>    SUM( IIF( E.Lassimilinami = .T. AND P.Iassimilid = .T. , H.n100 + H.n100_2 + H.n120 + H.n120_2 , 0.00 )) totHoAs ;
>    FROM ;
>    Horaire H INNER JOIN PAUSE P ON  H.cpauseid2 = P.iid AND H.ddate BETWEEN D_debut AND d_fin ;
>    INNER JOIN Employee E ON  E.iid = H.iemployeeid;
>    GROUP BY 1;
>    INTO CURSOR temp
>

>
>This code don't function...
>
>

> SELECT H.iemployeeid  ,;
>    COUNT(*) totaljo ,;
>    SUM( IIF( E.Lassimilinami = .F. OR P.Iassimilid = .F. , H.n100 + H.n100_2 + H.n120 + H.n120_2 , 0.00 )) totHoNAs ,;
>    SUM( IIF( E.Lassimilinami = .T. AND P.Iassimilid = .T. , H.n100 + H.n100_2 + H.n120 + H.n120_2 , 0.00 )) totHoAs ;
>    FROM ;
>    Horaire H INNER JOIN PAUSE P ON  H.cpauseid = P.iid AND H.ddate BETWEEN D_debut AND d_fin ;
>    INNER JOIN Employee E ON  E.iid = H.iemployeeid;
>     UNION;
>    SELECT H.iemployeeid  ,;
>    COUNT(*) totaljo ,;
>    SUM( IIF( E.Lassimilinami = .F. OR P.Iassimilid = .F. , H.n100 + H.n100_2 + H.n120 + H.n120_2 , 0.00 )) totHoNAs ,;
>    SUM( IIF( E.Lassimilinami = .T. AND P.Iassimilid = .T. , H.n100 + H.n100_2 + H.n120 + H.n120_2 , 0.00 )) totHoAs ;
>    FROM ;
>    Horaire H INNER JOIN PAUSE P ON  H.cpauseid2 = P.iid AND H.ddate BETWEEN D_debut AND d_fin ;
>    INNER JOIN Employee E ON  E.iid = H.iemployeeid;
>    GROUP BY 1;
>    INTO CURSOR temp
>

>
>Thank in advance
>
>bernhart

Union is not necessary.

If I remember good:

 SELECT ;
   H.iemployeeid  ;
 , COUNT(*) totaljo ;
 , SUM( IIF( E.Lassimilinami AND P.Iassimilid , 0.00, H.n100 + H.n100_2 + H.n120 + H.n120_2 )) totHoNAs ;
,  SUM( IIF( E.Lassimilinami AND P.Iassimilid , H.n100 + H.n100_2 + H.n120 + H.n120_2 , 0.00 )) totHoAs ;
    FROM	Horaire H ;
    	JOIN	PAUSE P 	ON  P.iid=EVL(H.cpauseid,H.cpauseid2)  ;
    	JOIN 	Employee E	ON  E.iid = H.iemployeeid;
	    WHERE H.ddate BETWEEN m.D_debut AND m.d_fin
    	GROUP BY 1;
    INTO CURSOR temp