Reputation: 219
I have a script which I'm trying to work out a running balance on a set of transactions:
So the key fields here is the Opening Balance. This will be the balance as of when the report is run. So the value will be the same for each "AccountId" in the query.
The Total Value is the value of the transaction that has taken place. The (No column name) is a Row Number that resets after each new account it finds in the result set -
ROW_NUMBER()OVER(PARTITION BY AccountId ORDER BY PostingDate)
What I am then doing is in the Balance field I want to do the following.
When the Row Number = 1, then it will use the Opening Balance and add to the Total Value. As you can see I am doing this fine.
However, what I am struggling to achieve is for each subsequent row, how do I calculate the Balance on the line below it.
So in the example above the first row shows a balance of 125.80. I want the 2nd row to be 226.98. So The Balance + Total Value for all rows that do not have a Row Number of 1.
Upvotes: 0
Views: 1973
Reputation: 2051
Select Balance = OpeningBalance +
Sum(TotalValue) Over (Partition By AccountId
Order By PostingDate
Rows Between Unbounded Preceding And Current Row)
From t;
The Rows Between
limits the sum to the rows prior to this one in the sort order, otherwise the sum would include all the rows in the partition.
Rows Between
is unnecessary in this case.
More on window functions.
Upvotes: 2
Reputation: 1269445
In SQL Server 2012+, you can do:
select (OpeningBalance +
sum(TotalValue) over (partition by AccountId order by PostingDate)
) as Balance
from t;
Upvotes: 5