Multiply value from next row to get monthly Index in SQL Server

I have the following data:

From this data, I want to calculate the Monthly index for each group (Index of current month * MonthlyReturn of Next Month).

Desired Output:

I am guessing the LEAD function would be helpful here but unable to get the desired result.

>Solution :

This is likely going to perform poorly at scale, but here’s a recursive option:

DECLARE @Table TABLE (Date DATE, IndexID INT, MonthlyReturn TINYINT, GroupName VARCHAR(7));
INSERT INTO @Table (Date, IndexID, MonthlyReturn, GroupName) VALUES
('2023-01-01', 10   , 0 , 'Group 1'),
('2023-02-01', NULL , 3 , 'Group 1'),
('2023-03-01', NULL , 6 , 'Group 1'),
('2023-01-01', 10   , 0 , 'Group 2'),
('2023-02-01', NULL , 4 , 'Group 2'),
('2023-03-01', NULL , 2 , 'Group 2');

;WITH rCTE AS (
SELECT Date, IndexID, MonthlyReturn, GroupName
  FROM @Table
 WHERE IndexID IS NOT NULL
UNION ALL
SELECT r.Date, a.IndexID*r.MonthlyReturn, r.MonthlyReturn, a.GroupName
  FROM rCTE a
    INNER JOIN @Table r
      ON a.Date = DATEADD(MONTH,-1,r.Date)
      AND a.GroupName = r.GroupName
)

SELECT *
  FROM rCTE
 ORDER BY GroupName, Date;

Note that I changed the names of the columns Index and Group to avoid the keywords, it’s bad practice to use illegal column names.
Also note how the demo data is expressed as full DDL/DML – this is very helpful when you’re asking a question.