1 2 3 4 5 6 7 8
| SELECT DISTINCT DATENAME(MONTH,[Date_Operation]) AS MOIS,DATENAME(YEAR,[Date_Operation]) AS ANNEE,[Date_Operation],[Date_Close],[HEURS],V.[IDCLIENT],V.[IDPRODUIT],SUM([ENTTREE]) AS [ENTTREE]
,SUM([SORTIE]) AS [SORTIE],SUM([RC]) AS [RC],[ENTREE] AS JAUGE
, (SUM(COALESCE([ENTTREE],0))+(COALESCE([ENTREE],0))-SUM(COALESCE([SORTIE],0))+SUM(COALESCE([RC],0)) ) AS ETAT_STOCK
,(LAST_VALUE([ENTREE]) over (partition by V.IDPRODUIT, V.IDCLIENT,DATENAME(MONTH,Date_Debut) order by DATENAME(MONTH,Date_Debut))-(SUM(COALESCE([ENTTREE],0))+(COALESCE([ENTREE],0))-SUM(COALESCE([SORTIE],0))+SUM(COALESCE([RC],0)))) AS ECART
FROM [VGESTIONSORTIESTATIONS] V
INNER JOIN [STOCKDEBUTPARSTATION] S ON S.IDCLIENT=V.IDCLIENT AND S.IDPRODUIT=V.IDPRODUIT AND S.Date_Debut=V.Date_Operation
WHERE [Date_Operation]!='' AND V.IDCLIENT='CL17' AND V.[IDPRODUIT]='SUP01' AND ([SORTIE])!=''
GROUP BY [Date_Operation],[Date_Close],[HEURS],V.[IDCLIENT],V.[IDPRODUIT],[ENTREE],Date_Debut |
Partager