Field cumulativity calculated using Sqlserver

Asked

Viewed 66 times

0

I have the following table:

inserir a descrição da imagem aqui

Therefore, I need to accumulate the value of the expression using SQL Server and the only way I could do it was using cursor; someone has another idea?

USE TEMPDB
GO

IF OBJECT_ID('TEMPDB..#T') IS NOT NULL 
    DROP TABLE #T
IF OBJECT_ID('TEMPDB..#TT') IS NOT NULL 
    DROP TABLE #TT

CREATE TABLE #T (SEQ INT, VLR DECIMAL (15,10))
CREATE TABLE #TT (SEQ INT, VLR DECIMAL (15,10) , VLR_ACUMULADO DECIMAL(15,10 ))

INSERT INTO #T (SEQ,VLR)  VALUES
 (1     ,1.0007967197)
,(2     ,1.0007967197)
,(3     ,1.0007967197)
,(4     ,1.0000000000)
,(5     ,1.0000000000)
,(6     ,1.0007967197)
,(7     ,1.0007967197)
,(8     ,1.0007967197)
,(9     ,1.0007967197)
,(10    ,1.0007967197)
,(11    ,1.0000000000)


DECLARE @SEQ INT, @VLR DECIMAL (15,10) , @VLR_ACUMULADO DECIMAL(15,10)


DECLARE CR CURSOR FOR 
SELECT SEQ, VLR 
FROM #T

OPEN CR
FETCH NEXT FROM CR INTO @SEQ, @VLR
WHILE @@FETCH_STATUS = 0
BEGIN 

    IF (@SEQ = 1) 
    BEGIN
        SET @VLR_ACUMULADO = @VLR
    END
    ELSE
    BEGIN
        SET @VLR_ACUMULADO = @VLR_ACUMULADO * @VLR

    END 

    INSERT INTO #TT (SEQ,VLR,VLR_ACUMULADO)
    VALUES (@SEQ, @VLR, @VLR_ACUMULADO)


    FETCH NEXT FROM CR INTO @SEQ, @VLR
END
CLOSE CR
DEALLOCATE CR

1 answer

1

There are some forms and one of them is with the use of recursive CTE.

-- código #1 v2
with Acumulado as (
SELECT SEQ, VLR, VLR as VLR_ACUMULADO 
  from #T
  where SEQ = 1

union all
SELECT T.SEQ, T.VLR, cast ((A.VLR_ACUMULADO * T.VLR) as decimal(15,10))
  from Acumulado as A
       inner join #T as T on T.SEQ = (A.SEQ +1)
)
INSERT into #TT (SEQ, VLR, VLR_ACUMULADO)
  SELECT SEQ, VLR, VLR_ACUMULADO
    from Acumulado;

Browser other questions tagged

You are not signed in. Login or sign up in order to post.