1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39
|
SELECT @a1:=if(enentrep.typemvt = '08',deentrep.nbrecolis1, 0 ) ,
@b1:=if(enentrep.typemvt = '08',deentrep.nbrecolis2, 0 ),
@c1:=if(enentrep.typemvt = '08',deentrep.tonnage, 0 ),
@a2:=if(enentrep.typemvt = '09',deentrep.nbrecolis1, 0 ),
@b2:=if(enentrep.typemvt = '09',deentrep.nbrecolis2, 0 ),
@c2:=if(enentrep.typemvt = '09',deentrep.tonnage, 0 ),
sum(if(enentrep.typemvt = '08' or enentrep.typemvt = '10',deentrep.nbrecolis1, 0 )) as ent2_s,
sum(if(enentrep.typemvt = '08' or enentrep.typemvt = '10',deentrep.nbrecolis2, 0 )) as ent2_c,
sum(if(enentrep.typemvt = '08' or enentrep.typemvt = '10',deentrep.nbrepalett, 0 )) as ent2_p,
sum(if(enentrep.typemvt = '08' or enentrep.typemvt = '10',deentrep.tonnage, 0 )) as ent2_t,
sum(if(enentrep.typemvt = '09' or enentrep.typemvt = '11',deentrep.nbrecolis1, 0 )) as sor2_s,
sum(if(enentrep.typemvt = '09' or enentrep.typemvt = '11',deentrep.nbrecolis2, 0 )) as sor2_c,
sum(if(enentrep.typemvt = '09' or enentrep.typemvt = '11',deentrep.nbrepalett, 0 )) as sor2_p,
sum(if(enentrep.typemvt = '09' or enentrep.typemvt = '11',deentrep.tonnage, 0 )) as sor2_t,
if(((@b1-@b2)+(@a1-@a2))<>0,(@c1-@c2)/((@b1-@b2)+(@a1-@a2)), 0) as m_moy,
chambre.code as code,
produit.designation as m_produit,
variete.designation as m_variete
FROM enentrep
, deentrep
, chambre
, variete
, produit
where enentrep.eecleunik = deentrep.eecleunik
and enentrep.chcleunik = chambre.chcleunik
and enentrep.vacleunik = variete.vacleunik
and variete.prcleunik = produit.prcleunik
and enentrep.cocleunik = _Param1
and enentrep.chcleunik = _Param2
and enentrep.vacleunik = _Param3
GROUP BY enentrep.cocleunik
,enentrep.chcleunik
,enentrep.vacleunik
order by enentrep.datemvt |
Partager