CODEWITHSUNDEEP
sqlsql-servercommon-table-expression
Greg Gum
Greg Gum

Reputation: 37909

How to calculate compound running total in SQL

I have a table like this:

Year, DividendYield
1950, .1
1951, .2
1952, .3

I now want to calculate the total running shares. In other words, if the dividend is re-invested in new shares, it will look now like this:

Original Number of Shares purchased Jan 1, 1950 is 1

1950, .1, 1.1 -- yield of .1 reinvested in new shares results in .1 new shares, totaling 1.1
1951, .2, 1.32 -- (1.1 (Prior Year Total shares) * .2 (dividend yield) + 1.1 = 1.32)
1953, .3, 1.716 -- (1.32 * .3 + 1.32 = 1.716)

The closest I have been able to come up with is this:

declare @startingShares int = 1
; with cte_data as (
    Select *, 
    @startingShares * DividendYield as NewShares, 
    (@startingShares * DividendYield) + @startingShares as TotalShares from DividendTest
)
select *, Sum(TotalShares) over (order by id) as RunningTotal from cte_data

But only the first row is correct.

Id  Year    DividendYield   NewShares   TotalShares RunningTotal
1   1950    0.10    0.10    1.10    1.10
2   1951    0.20    0.20    1.20    2.30
3   1953    0.30    0.30    1.30    3.60

How do I do this with SQL? I was trying not to resort to a loop to process this.

Upvotes: 3

Views: 555

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

You want a cumulative multiplication. I think a correlated CTE is actually the simplest solution:

with tt as (
      select t.*, row_number() over (order by year) as seqnum
      from t
     ),
     cte as (
      select tt.year, convert(float, tt.yield) as yield, tt.seqnum
      from tt
      where seqnum = 1
      union all
      select tt.year, (tt.yield  + 1) * (cte.yield + 1) - 1, tt.seqnum
      from cte join
           tt
           on tt.seqnum = cte.seqnum + 1
     )
select cte.*
from cte;

Here is a db<>fiddle.

You can also phrase this using logs and exponents:

select t.*,
       exp(sum(log(1 + yield)) over (order by year)) - 1
from t;

This should be fine for most purposes, but I find that for longer series this introduces numerical errors more quickly than the recursive CTE.

Upvotes: 5

Related Questions