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)
Select Balance = OpeningBalance + Sum(TotalValue) Over (Partition By AccountId Order By PostingDate Rows Between Unbounded Preceding And Current Row) From t;
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.