0

I am trying to come up with a way to create a running balance in my select statement.

enter image description here

So I am alreay pulling back an Opening and Closing Balance per AccountId - I am basing that where the Rank column is equal to one.

From here what I want to achieve is for every subsequent row is have the previous BalanceCarriedForward placed into the row below it and the BalanceBroughtForward.

So the first row we start with 150.77 and we add the Total Value of 113.19 to get the BalanceCarriedForward in this case 263.96.

What I would want in in Rank 2 (row 2) the BalanceBroughtForward is 263.96 and then the BalanceCarriedForward in this case would be 377.15....and so on.

I'm struggling to work out the best way to populate the remaining BalanceBroughtForward and BalanceCarriedForward values from the 0.00 at the moment.

Rank 1 will always have starting figures.

Thanks

aynber
  • 22,380
  • 8
  • 50
  • 63
ikilledbill
  • 211
  • 1
  • 3
  • 17
  • 1
    Does this answer your question? [Calculate a Running Total in SQL Server](https://stackoverflow.com/q/860966/3484879) – Thom A Dec 18 '19 at 12:37
  • Does this answer your question? [Calculate a Running Total in SQL Server](https://stackoverflow.com/questions/860966/calculate-a-running-total-in-sql-server) – Eric Brandt Dec 18 '19 at 13:34

1 Answers1

0

Look in to SQL window functions, take a look at the below:

create table TestTable (
Year int,
PeriodNumber int,
AccountId int,
TotalValue decimal, 
Rank int
);

insert into TestTable (Year,PeriodNumber,AccountId,TotalValue,Rank) values (2019,1,14023,113.19,1);
insert into TestTable (Year,PeriodNumber,AccountId,TotalValue,Rank) values (2019,2,14023,113.19,2);
insert into TestTable (Year,PeriodNumber,AccountId,TotalValue,Rank) values (2019,3,14023,113.19,3);
insert into TestTable (Year,PeriodNumber,AccountId,TotalValue,Rank) values (2019,4,14023,-339.57,4);
insert into TestTable (Year,PeriodNumber,AccountId,TotalValue,Rank) values (2019,5,14023,113.19,5);
insert into TestTable (Year,PeriodNumber,AccountId,TotalValue,Rank) values (2019,6,14023,113.19,6);
insert into TestTable (Year,PeriodNumber,AccountId,TotalValue,Rank) values (2019,7,14023,-339.579,7);
insert into TestTable (Year,PeriodNumber,AccountId,TotalValue,Rank) values (2019,8,14023,113.19,8);
insert into TestTable (Year,PeriodNumber,AccountId,TotalValue,Rank) values (2019,9,14023,113.19,9);

select *,
sum(TotalValue) over (order by Rank) as RunningTotal,
sum(TotalValue) over (order by Rank rows between unbounded preceding and 1 preceding) as PrevRunningTotal
from TestTable
user85567
  • 3
  • 3