How do I get the value of the previous row and perform calculation based on it?-CodePudding

How do I get the previous row value for field1 and multiply it times 2?

Initially, there will always be something in the first row to start things off. All other field1 values will always be null to begin with.

DECLARE @varTable1 Table
(
[id] [int],
[field1] [decimal](18,4)
)

INSERT INTO @varTable1
VALUES
(1,20),
(2,NULL),
(3,NULL),
(4,NULL)

SELECT * FROM @varTable1

Expected:

id  field1
1   20.0000
2   40.0000
3   80.0000
4   160.0000

CodePudding user response：

Seems like you can just SUM the value of field1 and then multiply it by a power of 2:

SELECT Id,
       SUM(field1) OVER () * POWER(2,ROW_NUMBER() OVER (ORDER BY Id) - 1)
FROM @varTable1 vT1;

db<>fiddle