ikilledbill
ikilledbill

Reputation: 219

TSQL - running total

I have a script which I'm trying to work out a running balance on a set of transactions:

image1

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

Answers (2)

mendosi
mendosi

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

Gordon Linoff
Gordon Linoff

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

Related Questions