1 2 3 4 5 6 7 8 9 10 11 12
| SELECT DISTINCT MONTH(V.[Date_Operation]) AS MOIS,YEAR(V.[Date_Operation]) AS ANNEE, V.Date_Operation,V.IDCLIENT,V.IDPRODUIT,sum(ENTTREE) AS ENTTREE
,SUM(SORTIE) AS SORTIE,SUM(RC) AS RC,(COALESCE(S.QUANTITE,0)) AS JAUGE
, (sum(COALESCE(ENTTREE,0))+(COALESCE(QUANTITE,0))-SUM(COALESCE(SORTIE,0))+SUM(COALESCE(RC,0))) AS ETAT_STOCK
, CASE WHEN LEAD((COALESCE(S.QUANTITE,0))) OVER(PARTITION BY V.IDPRODUIT, V.IDCLIENT ORDER BY V.Date_Operation)>0 THEN LEAD((COALESCE(QUANTITE,0))) OVER(PARTITION BY V.IDPRODUIT, V.IDCLIENT ORDER BY V.[Date_Operation])-(sum(COALESCE(ENTTREE,0))+(COALESCE(QUANTITE,0))-SUM(COALESCE(SORTIE,0))+SUM(COALESCE(RC,0))) END AS ECART
FROM [VGESTIONSORTIESTATIONS] V
LEFT JOIN [VVSTOCKDEBUTPARCUVEETPARSTATION] S ON S.IDCLIENT=V.IDCLIENT AND S.IDPRODUIT=V.IDPRODUIT AND S.Date_Operation=V.Date_Operation
INNER JOIN [CLIENT] C ON C.IDCLIENT=V.IDCLIENT
INNER JOIN [PRODUITSS] P ON P.IDPRODUIT=V.IDPRODUIT
WHERE V.[IDCLIENT]='CL17' AND V.[Date_Operation] BETWEEN '2018-01-04' AND '2018-01-07' AND MONTH(V.[Date_Operation])!='' and V.[Date_Operation]!=''
GROUP BY V.Date_Operation,V.IDCLIENT,V.IDPRODUIT,S.Date_Operation,[QUANTITE]
GO |
Partager