1

Possible Duplicate:
Calculate a Running Total in SqlServer

I am creating a transaction table. Ive decided not to store the balance of each user anywhere. What I am plannign to do is, every month I will "BRING BALANCE FORWARD" and caclulate the balance from the balance bought forward MINUS debits PLUS credits.

My table looks like this

ID int primary key
userID int foriegn key
TransactionDate datetime
description varchar(200)
amount money
balanceBoughtForward bit

I am planning on writting a function that gets the latest record that is a balanceeboughtforward, then using that id to calculate the balance with the running total in a view with an extra column "BALANCE"

But I cant figure out how to create a running total column?

Community
  • 1
  • 1
Michael
  • 8,229
  • 20
  • 61
  • 113
  • 1
    Also, if you're on SQL 2012, the running total is a more efficient query. Not really sure how your `balanceBoughtForward` is supposed to act though (also has a typo) – billinkc Apr 25 '12 at 00:53
  • Can I assume the column "BALANCE" will have only one row that is not null at any given time? – cctan Apr 25 '12 at 01:08

1 Answers1

0

Perhaps I'm not getting it or your question isn't clear enough. Isn't this solved with an aggregate function (the SUM function more precisely).

Ex: 

select user_id, (sum(credit) - sum(debit)) balance
from table
where
some condition
group by
user_id
Arion
  • 31,011
  • 10
  • 70
  • 88
J. Bend
  • 299
  • 2
  • 3
  • 14