SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE PDIndicatorsHistory 
(
	RecId int PRIMARY KEY IDENTITY(1,1),
	ItemId int,
	ReportDate datetime,
	PD int
);
  
INSERT INTO PDIndicatorsHistory (ItemId,ReportDate,PD) VALUES
(1, '2025-07-06', 2), (1, '2025-07-07', NULL), (1, '2025-07-08', NULL);

SELECT * FROM PDIndicatorsHistory;


RecId
ItemId
ReportDate
PD


1
1
2025-07-06 00:00:00.000
2

2
1
2025-07-07 00:00:00.000
null

3
1
2025-07-08 00:00:00.000
null


CREATE FUNCTION dbo.GetPD 
(
    @prev_RecId INT,
    @next_RecId int,
    @prev_PD INT
)
RETURNS int
AS

BEGIN
    RETURN @prev_PD * 2;
END;
WITH
cte1 AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY ItemId ORDER BY ReportDate ASC) rn
    FROM PDIndicatorsHistory
    ),
cte2 AS (
    SELECT RecId, ItemId, ReportDate, PD, rn
    FROM cte1
    WHERE rn = 1
    UNION ALL
    SELECT cte1.RecId,
	       cte1.ItemId,
	       cte1.ReportDate,
	       dbo.GetPD(cte2.RecId, cte1.RecId, cte2.PD),
           cte1.rn
    FROM cte1
    JOIN cte2 ON cte1.ItemId = cte2.ItemId
             AND cte1.rn = cte2.rn + 1
    )
SELECT * FROM cte2;


RecId
ItemId
ReportDate
PD
rn


1
1
2025-07-06 00:00:00.000
2
1

2
1
2025-07-07 00:00:00.000
4
2

3
1
2025-07-08 00:00:00.000
8
3


WITH
cte1 AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY ItemId ORDER BY ReportDate ASC) rn
    FROM PDIndicatorsHistory
    ),
cte2 AS (
    SELECT RecId, ItemId, ReportDate, PD, rn
    FROM cte1
    WHERE rn = 1
    UNION ALL
    SELECT cte1.RecId,
	       cte1.ItemId,
	       cte1.ReportDate,
	       dbo.GetPD(cte2.RecId, cte1.RecId, cte2.PD),
           cte1.rn
    FROM cte1
    JOIN cte2 ON cte1.ItemId = cte2.ItemId
             AND cte1.rn = cte2.rn + 1
    )
UPDATE PDIndicatorsHistory
SET PD = cte2.PD
FROM cte2
WHERE PDIndicatorsHistory.RecID = cte2.RecId;

SELECT * FROM PDIndicatorsHistory;


RecId
ItemId
ReportDate
PD


1
1
2025-07-06 00:00:00.000
2

2
1
2025-07-07 00:00:00.000
4

3
1
2025-07-08 00:00:00.000
8
RecId	ItemId	ReportDate	PD
1	1	2025-07-06 00:00:00.000	2
2	1	2025-07-07 00:00:00.000	null
3	1	2025-07-08 00:00:00.000	null