Running Sum in a SQL Server View

[CanNeverThinkOfaName]

I have a table which gives me a bill amount on a certain date but there is no balance as at any given date.

I need to take an account eg account no = 60000120
Then I need a running sum of all the balances up to today eg

AccountNo TransactionDate Amount
60000120 19/01/2001 200
60000120 19/12/2000 200
60000120 21/11/2000 200
60000120 19/10/2000 200
60000120 19/09/2000 200

so on the 19/10/2000 I want the balance to be £400( 19/9/2000 £200 + 19/10/2000 £200)
on the 19/11/2000 the balance = £600

etc

HELP!!

SQL used for resultset;
SELECT DISTINCT TOP 100 PERCENT RELN_ID AS AccountNo, T_DATE AS TransactionDate, AMNT AS Amount
FROM ImportData.dbo.[CMAll Financial Transactions]
WHERE (RELN_ID = 60000120)
ORDER BY T_DATE DESC


I need a take a bunch of accounts on a certain TransactionDate and get the total sum of each accounts balance per that TransactionDate....
so i need a running sum per account and then take the balance on that TransactionDate for a certain bunch of accounts

eg accounts 60000120,60000121,60000122
TransactionDate = 25/8/2004
The sum of their balances = £1000 (550+50+400)
(550,50,400 are runnign sums up to 25/8/04)


Thanks for all your help

Maria

 

[vongrunt]

First, why DISTINCT?
Second, try with derived table:

select sum(X.AccountBalance)
from
(	select  RELN_ID, sum(AMNT) as AccountBalance
	from ImportData.dbo.[CMAll Financial Transactions]
	where T_DATE <= '25/8/2004'
	group by RELN_ID
) X

 

[CanNeverThinkOfaName]

hi vongrunt

Thanks sooo much for the help..its been bugging me how to do that!
It works great and really quickly too!

Maria