Update all the following values in column depending on previous value-CodePudding

I am looking to do a "cascading" update on a column with all null values except the "first". The order is determined by date ascending. I have this table

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[StockA]
(
    [date] [date] NOT NULL,
    [PercentChange] [decimal](19, 6) NULL,
    [Price] [decimal](16, 2) NULL
) ON [PRIMARY]
GO

INSERT INTO [dbo].[StockA] ([date], [PercentChange], [Price]) 
VALUES (CAST(N'2021-08-19' AS Date), CAST(-0.005100 AS Decimal(19, 6)), NULL)

INSERT INTO [dbo].[StockA] ([date], [PercentChange], [Price]) 
VALUES (CAST(N'2021-08-20' AS Date), CAST(0.013000 AS Decimal(19, 6)), NULL)

INSERT INTO [dbo].[StockA] ([date], [PercentChange], [Price]) 
VALUES (CAST(N'2021-08-23' AS Date), CAST(0.015400 AS Decimal(19, 6)), NULL)

INSERT INTO [dbo].[StockA] ([date], [PercentChange], [Price]) 
VALUES (CAST(N'2021-08-24' AS Date), CAST(0.009500 AS Decimal(19, 6)), NULL)

INSERT INTO [dbo].[StockA] ([date], [PercentChange], [Price]) 
VALUES (CAST(N'2021-08-18' AS Date), CAST(0.010000 AS Decimal(19, 6)), CAST(100.00 AS Decimal(16, 2)))
GO

SELECT * 
FROM StockA 
ORDER BY date ASC

The four NULL values should be calculated based on the previous, starting with first NULL value on date 2021-08-19 gives is a calculation of 100(this is the previous value) * (1 PercentChange) = 99.49

I have tried this

WITH CTE AS
(
    SELECT 
        date, PercentChange, Price,
        (LAG(Price) OVER (Order by date))*(1 PercentChange) AS NextPrice
    FROM
        StockA
)
UPDATE CTE
SET Price = NextPrice;

which incorrectly gives me this:

The calculation is correct. The row 2021-08-19 should be 99.49. Nothing wrong there. But something is wrong with my update statement apparently. If I run my CTE query again, the correct value is added to the next row and disappearing from the previous.

Does anybody know how to fill the entire "Price" column, so that there are no NULLS, with calculated values that are based on the "first" (ordered by date ascending) Price?

I am using SQL Server 2019.

CodePudding user response：

One method uses logs to emulate a product aggregation function:

with toupdate as (
      select a.*,
             exp(sum(log(1   percent_change)) over (order by date)) / (1   first_value(percent_change) over (order by date)) as factor,
             first_value(price) over (order by date) as orig_price
      from stockA
     )
update toupdate
    set price = orig_price * factor
    where price is null;