1 2 3 4 5 6 7 8 9 10 11 12
| WITH SOLDE
(MOIS, ANNEE, [Date_Operation],[IDCLIENT],[IDPRODUIT],[NumeroBon],[ENTTREE],[INDEX_OUVERTURE],[INDEX_FERMETURE],[SORTIE]
,[RC],[REPORT],[JAUGE],ECART,ETAT_STOCK) AS (SELECT DATENAME(MONTH,[Date_Operation]) AS MOIS,DATENAME(YEAR,[Date_Operation]) AS ANNEE, [Date_Operation],[IDCLIENT],[IDPRODUIT],[NumeroBon],[ENTTREE],[INDEX_OUVERTURE],[INDEX_FERMETURE],[SORTIE]
,[RC],[REPORT],[JAUGE]
, (CASE WHEN COALESCE([JAUGE],0)>0 THEN (COALESCE([JAUGE],0))-(SUM((COALESCE([ENTTREE],0)+COALESCE([REPORT],0)-COALESCE([SORTIE],0)+COALESCE([RC],0)-COALESCE(PERTES,0))) OVER (PARTITION BY IDCLIENT,[IDPRODUIT] ORDER BY [Date_Operation],[ENTTREE],[SORTIE])) END) AS ECART
, SUM((COALESCE([ENTTREE],0)+COALESCE([REPORT],0)-COALESCE([SORTIE],0)+COALESCE([RC],0))) OVER (PARTITION BY IDCLIENT,[IDPRODUIT] ORDER BY [Date_Operation],[ENTTREE],[SORTIE]) AS ETAT_STOCK
from [dbo].[VGESTIONSORTIESTATIONS] )
SELECT MOIS, ANNEE, [Date_Operation],[IDCLIENT],[IDPRODUIT],[NumeroBon],[ENTTREE],[INDEX_OUVERTURE],[INDEX_FERMETURE],[SORTIE]
,[RC],[REPORT],[JAUGE],ECART
, SUM((COALESCE([ENTTREE],0)+COALESCE([REPORT],0)-COALESCE([SORTIE],0)+COALESCE([RC],0))+COALESCE([ECART],0)) OVER (PARTITION BY IDCLIENT,[IDPRODUIT] ORDER BY [Date_Operation],[ENTTREE],[SORTIE]) AS ETAT_STOCK
FROM SOLDE
WHERE [Date_Operation]!='' AND ([ENTTREE]!='' OR [REPORT]!='' OR [SORTIE]!='' OR [RC]!='' OR [ECART]!='')AND IDCLIENT='CL17' AND [IDPRODUIT]='SUP01' |
Partager