Skip to content
Advertisement

Calculate cumulative value in update query using SQL

I have the following database table:

I would like to update the Index value using the following formula:

Expected Result: (Index to be calculated order by Date asc)

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.

Advertisement

Answer

You want a cumulative sum. In SQL Server, you should use an updatable CTE:

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.

User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement