1 2 3 4 5 6 7 8 9 10 11 12
|
SELECT 0 as iOrder,'Article' as Id,a.ArtCod, a.ArtDes, ArtPrx,SUM(CptPhy) As CptPh,SUM(CptLog) As CptLg, iif((SUM(CptPhy)-SUM(CptLog))>0,(SUM(CptPhy)-SUM(CptLog)),0) as EcrP, iif((SUM(CptLog)-SUM(CptPhy))>0,(SUM(CptLog)-SUM(CptPhy)),0) as EcrN, 0 as MvtE,0 as MvtS
FROM Liste_Comptage lc, Comptage c, Reference r, Article a, Marque m
WHERE lc.MrqCod=m.MrqCod AND lc.CptNum=c.CptNum AND c.RefFou = r.RefFou AND r.ArtCod=a.ArtCod And lc.MrqCod =:mrq
GROUP BY a.ArtCod, a.ArtDes, ArtPrx
UNION
Select 1 as iOrder,'Mouvement' as Id,a.ArtCod, a.ArtDes, ArtPrx,0 As CptPh,0 As CptLg, 0 as EcrP, 0 as EcrN, SUM(iif(DmvFlg='E',DmvQte,0)) As MvtE, SUM(iif(DmvFlg='S',DmvQte,0)) As MvtS
FROM Reference r, Detail_mouvement d, Article a, marque m
WHERE d.RefFou=r.Reffou AND r.ArtCod=a.ArtCod AND m.MrqCod=a.MrqCod And DmvInv is Null And m.MrqCod =:mrq
ORDER BY a.ArtCod,1 |
Partager