BonJour fabio,
I remember also:)
if H.cpauseid # 0 AND H.cpauseid2 # 0 ........?
bernhart
>>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
>
