Cumulating Value of Current Row + Sum of Previous Rows

SUM from previous ROW values

You can use the cumulative sum function (ANSI SQL):

with t as (
      <your query here>
     )
select t.*,
       sum(receipt) over (order by date, shift) as totalreceipt,
       sum(issue) over (order by date, shift) as totalissue,
       sum(issue - receipt) over (order by date, shift) as variance
from t;

Cumulatively sum a portion of a previous value with its next value

I would use a for loop to do this. It's important to initialize a vector first, especially if you're working with a large data set.

# initialize
newx <- vector("numeric", length(df$x))
newx[1] <- df$x[1]

for(i in 2:length(df$x)){
  newx[i] <- df$x[i] + (0.8 * newx[i-1])
}

newx
# [1]  1.00000  2.80000  5.24000  8.19200 11.55360 15.24288 19.19430 23.35544 27.68435 32.14748

SQL query to calculate sum of row with previous row

SELECT t.date, (
    SELECT SUM(numsubs) 
    FROM mytable t2 
    WHERE t2.date <= t.date
) AS cnt
FROM mytable t

Sum of previous rows and multiply with value from another column

While your post is lacking some vital information...I don't necessarily blame you because it's a difficult problem to both explain and to solve.

It looks like you're basically trying to come up with a compounding interest calculator. Except in this case, the rate changes every year.

To calculate the PRODUCT aggregate of your forecasts, I found this blog post:
https://blog.jooq.org/2018/09/21/how-to-write-a-multiplication-aggregate-function-in-sql/

It just required a very tiny bit of tweaking.

This is my answer:

DECLARE @StartingYear int = 2021,
        @StartingBudget decimal(12, 2);

SELECT @StartingBudget = yt.budget
FROM #YourTable yt
WHERE yt.startyear = @StartingYear

SELECT yt.startyear, yt.division, yt.account, yt.budget, yt.forecast, YearBudget = yt.budget, PrevYearDiff = yt.budget
FROM #YourTable yt
WHERE yt.startyear = @StartingYear
UNION ALL
SELECT x.startyear, x.division, x.account, x.budget, x.forecast
     , YearBudget = CONVERT(decimal(10,2), x.SumProdBudget)
     , PrevYearDiff = CONVERT(decimal(10,2), x.SumProdBudget - LAG(x.SumProdBudget,1,@StartingBudget) OVER (ORDER BY x.startyear))
FROM (
    SELECT yt.startyear, yt.division, yt.account, yt.budget, yt.forecast
        , SumProdBudget = EXP(SUM(LOG(1+yt.forecast)) OVER (ORDER BY yt.startyear)) * @StartingBudget
    FROM #YourTable yt
    WHERE yt.startyear > @StartingYear
) x

Returns:

| startyear | division | account | budget     | forecast    | YearBudget | PrevYearDiff | 
|-----------|----------|---------|------------|-------------|------------|--------------| 
| 2021      | 40       | 4100    | 5122952.22 | 0.012306656 | 5122952.22 | 5122952.22   | 
| 2022      | 40       | 4100    | 0.00       | 0.011424198 | 5181477.84 | 58525.62     | 
| 2023      | 40       | 4100    | 0.00       | 0.010431491 | 5235528.38 | 54050.54     | 
| 2024      | 40       | 4100    | 0.00       | 0.009311863 | 5284280.90 | 48752.52     | 
| 2025      | 40       | 4100    | 0.00       | 0.008324122 | 5328267.90 | 43987.00     | 
| 2026      | 40       | 4100    | 0.00       | 0.007763793 | 5369635.47 | 41367.57     |

The key to this whole thing is this line:

SELECT SumProdBudget = EXP(SUM(LOG(1+yt.forecast)) OVER (ORDER BY yt.startyear)) * @StartingBudget

This is saying to return a running product of all previous forecasts, then multiply that by the original budget. This will produce the budget for each year, technically based on the budget of the previous year.

Then once you have that, I stuck it in a sub-query to find the difference between the year and its previous year.

Sample Data:

IF OBJECT_ID('tempdb..#YourTable','U') IS NOT NULL DROP TABLE #YourTable; --SELECT * FROM #YourTable
CREATE TABLE #YourTable (
    startyear   int             NOT NULL,
    division    int             NOT NULL,
    account     int             NOT NULL,
    budget      decimal(12, 2)  NOT NULL,
    forecast    decimal(10, 9)  NOT NULL,
);

INSERT INTO #YourTable (startyear, division, account, budget, forecast)
VALUES (2021, 40, 4100, 5122952.22, 0.012306656)
     , (2022, 40, 4100, 0         , 0.011424198)
     , (2023, 40, 4100, 0         , 0.010431491)
     , (2024, 40, 4100, 0         , 0.009311863)
     , (2025, 40, 4100, 0         , 0.008324122)
     , (2026, 40, 4100, 0         , 0.007763793)
     , (2027, 40, 4100, 0         , 0.007557735)
     , (2028, 40, 4100, 0         , 0.007357883)
     , (2029, 40, 4100, 0         , 0.007160051)
     , (2030, 40, 4100, 0         , 0.006953345)
     , (2031, 40, 4100, 0         , 0.006737952)
     , (2032, 40, 4100, 0         , 0.006535297)
     , (2033, 40, 4100, 0         , 0.006364179)
     , (2034, 40, 4100, 0         , 0.006213237)
     , (2035, 40, 4100, 0         , 0.006085724)
     , (2036, 40, 4100, 0         , 0.005944279)
     , (2037, 40, 4100, 0         , 0.005758285)
     , (2038, 40, 4100, 0         , 0.005559474)
     , (2039, 40, 4100, 0         , 0.005360105)
     , (2040, 40, 4100, 0         , 0.005163794)
     , (2041, 40, 4100, 0         , 0.004972228);

Sum of values from previous row columns + value of current row

In SQL Server 2008, you can use cross apply:

select t.*, tt.GSumABC
from t cross apply
     (select sum(valueA + valueB + ValueC) as GSumABC
      from t t2
      where t2.id <= t.id
     ) tt;

You can also phrase this as a correlated subquery:

select t.*,
       (select sum(valueA + valueB + ValueC) as GSumABC
        from t t2
        where t2.id <= t.id
       ) as GSumABC
from t;