Calculate cumulative value in update query using SQL

Tags: , ,



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.

Answer

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 date, index, and 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.



Source: stackoverflow