miércoles, 25 de septiembre de 2013

Usando CTE para Sumas Acumulativas SQL SERVER

WITH CTE
AS

(SELECT TOP 1 ROW_NUMBER() OVER(ORDER BY T.IdTransaccion ASC) AS Rn, 
        T.IdTransaccion,T.IdSolicitud,CONVERT(FLOAT,T.Monto)Monto , CONVERT(FLOAT,T.Monto) AS running_sum 
 FROM Prestamo.Transacciones T WHERE t.IdSolicitud=4

UNION ALL

SELECT A.Rn,A.IdTransaccion,A.IdSolicitud,A.Monto,A.Monto + CONVERT(FLOAT,T.running_sum)  running_sum  
FROM (SELECT ROW_NUMBER() OVER(ORDER BY T2.IdTransaccion ASC) AS Rn, T2.IdTransaccion,T2.IdSolicitud,CONVERT(FLOAT,T2.Monto) Monto ,T2.Monto R
          FROM dbo.Transacciones T2 
          WHERE  t2.IdSolicitud=4) A
     INNER JOIN CTE T ON T.Rn +1=A.Rn AND A.Rn>1 
)

SELECT * FROM CTE
ORDER BY RN
OPTION (maxrecursion 0)



No hay comentarios.:

Publicar un comentario