Calculate cumulative value in update query using SQL

Question

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.

Does this answer your question? [How to get cumulative sum](https://stackoverflow.com/questions/2120544/how-to-get-cumulative-sum) — Thom A, Apr 28 '21 at 09:48
@Larnu - Thanks, I need to update the values in the table in this case. — developer, Apr 28 '21 at 09:49
That doesn't change the solution though. You still want a windowed `SUM`, as demonstrated in the second answer. — Thom A, Apr 28 '21 at 09:50
also, I need to start with 100 as a starting point. Do you have an example of the query? Thanks. — developer, Apr 28 '21 at 09:51
*"I need to start with 100 as a starting point."* `100 + {Cumulative Sum}...` *"Do you have an example of the query?"* Yes, in the [answer](https://stackoverflow.com/a/13331102/2029983) in the linked suggested duplicate. If you don't understand the linked duplicate, what about it don't you understand? Have you tried to implement it? Why didn't it work? What was that attempt to implement it? — Thom A, Apr 28 '21 at 09:53
Do you actually want to backfill the data or just calculate it on querying the existing data? Also by _"previous month data"_ is that always the previous row order by date? Or the row exactly 1 month different? — Jamiec, Apr 28 '21 at 10:04
@Jamiec, just calculate on querying the existing data. "previous month data" is that always the previous row order by date. — developer, Apr 28 '21 at 10:28
Then you want Stu's answer just without the update (ie, what is in the CTE!) — Jamiec, Apr 28 '21 at 10:35

score 2 · Answer 1 · answered Apr 28 '21 at 10:00

2

To implement as an update of your existing table you need to build your result and join back to your table in order to update it. I'm using date here to join on from your example but you probably have a proper key you should use:

with r as (
    select [date] , 100+Sum([return]*100) over(order by [date]) [index]
    from t
)
update t set
    t.[index]=r.[index]
from r join t on t.[date]=r.[date]

answered Apr 28 '21 at 10:00

Stu

30,392
6
14
33

Thanks @Stu. Is there anyway to implement Index = (Previous_Month_Index * Return) + Previous_Month_Index instead. For the first month, Previous_Month_Index would be 100. – developer Apr 28 '21 at 10:50
@developer . . . This is not the best solution. A `join` is not needed. – Gordon Linoff Apr 28 '21 at 11:39
thanks @GordonLinoff, Unfortunately I have to delete this question. I have posted another question here: https://stackoverflow.com/questions/67299495/calculate-cumulative-product-value – developer Apr 28 '21 at 11:40
1

I agree Gordon's suggestion is preferred and more succinct - I always forget you can update in a CTE – Stu Apr 28 '21 at 11:43
@developer . . . There is no reason to delete this question. – Gordon Linoff Apr 28 '21 at 12:33
Ok, thanks - I will keep it, hope it will be useful to others. – developer Apr 28 '21 at 14:58

score 1 · Accepted Answer · answered Apr 28 '21 at 10:47

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.

Calculate cumulative value in update query using SQL

2 Answers2