I have the following database table:
Date Return Index 01-01-2020 0.1 Null 01-02-2020 0.2 Null 01-03-2020 0.3 Null
I would like to update the Index value using the following formula:
Index = (100 * Return) + Previous_Month_Index (if Previous_Month_Index is not available, use 100)
Expected Result: (Index to be calculated order by Date asc)
Date Return Index 01-01-2020 0.1 110 -- (100 + 10) 01-02-2020 0.2 130 -- (110 + 20) 01-03-2020 0.3 160 -- (130 + 30)
How can I do this using SQL? I am currently using cursor to calculate this but it is not a recommended way to calculate this.
You want a cumulative sum. In SQL Server, you should use an updatable CTE:
with toupdate as ( select t.*, 100+Sum(return * 100) over (order by date) as new_index from t ) update toupdate set index = new_index;
Note that columns name such as
return are really bad choices, because they are SQL keywords. I have not escaped them in the above logic (I think escaped names just clutter queries). I hope you have better naming conventions in your actual tables.