1 2 3 4 5 6 7 8 9 10 11 12
|
CREATE VIEW [dbo].[SOLDECLIENTSS]
AS
select G.[Numero],DATENAME(MONTH,G.[Date_Entree]) AS MOIS,DATENAME(YEAR,G.[Date_Entree]) AS ANNEE, G.[Date_Entree], [NumeroBon],[IDPRODUIT],[Pusortie],[LIBELLE],G.[IDCLIENT], [Versement] AS VERSEMENT,[NUMEROPIECES],[BANQUE], [DEPENSE], [MONTANT],[AUTRES],AUTRES_CREDIT,MONTANTJAUGECLIENTS,G.IDBANQUES
,CASE WHEN [CESSION]!='' THEN [CESSION] ELSE [SORTIE] END AS [SORTIE]
, SUM([REPORT]+[Versement]+[DEPENSE]+COALESCE(G.[AUTRES_CREDIT],0)-COALESCE(G.[MONTANT],0)-COALESCE(G.[AUTRES],0)) OVER (PARTITION BY G.[IDCLIENT] ORDER BY G.[Date_Entree],[NumeroBon],[Versement],[DEPENSE] ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS SOLDE_AVANT
, SUM([REPORT]+[Versement]+[DEPENSE]+COALESCE(G.[AUTRES_CREDIT],0)-COALESCE(G.[MONTANT],0)-COALESCE(G.[AUTRES],0)) OVER (PARTITION BY G.[IDCLIENT] ORDER BY G.[Date_Entree],[NumeroBon],[Versement],[DEPENSE]) AS SOLDE
, SUM([REPORT]+[Versement]+[DEPENSE]+COALESCE(G.[AUTRES_CREDIT],0)-COALESCE(G.[MONTANT],0)-COALESCE(G.[AUTRES],0)) OVER (PARTITION BY G.[IDCLIENT] ORDER BY G.[Date_Entree],[NumeroBon],[Versement],[DEPENSE]) AS SOLDE_APRES
FROM [GESTIONSTOCKPARDEPOT] G
LEFT OUTER JOIN [VMONTANTJAUGECLIENT] V ON G.IDCLIENT=V.IDCLIENT AND G.[Date_Entree]=V.[Dates]
LEFT OUTER JOIN [BANQUE] B ON B.IDBANQUES=G.IDBANQUES
GO |
Partager