Calculate running balance

[OldSlowDog]

I need to calculate a running balance for each row. The rows are sorted and the beginning balance 167230 is given.

My job is to use 167230 as the beginning balance and calculate the rest of the balances by date.

The formular is: daily bal = prev bal - Amt1 + Amt2 + Amt3

Here is the sample data:

ID TranDate Amt1 Amt2 Amt3 RunBal
1 9/30/10 2:41 PM 8182 0 0 167230
2 10/1/10 6:22 AM 8320 0 0 0
3 10/1/10 9:21 AM 8433 0 0 0
4 10/2/10 6:30 AM 0 31498 17 0
5 10/2/10 6:45 AM 0 29491 0 0
6 10/2/10 7:00 AM 0 29394 0 0
7 10/2/10 8:32 AM 8079 0 25 0
8 10/2/10 8:39 AM 8034 0 0 0
9 10/2/10 9:12 AM 8054 0 0 0
10 10/4/10 4:19 AM 8079 0 0 0
11 10/4/10 4:43 AM 8437 0 56 0
12 10/4/10 5:16 AM 7602 0 0 0
13 10/4/10 7:20 AM 7877 0 0 0
14 10/4/10 8:00 AM 0 31170 0 0

Here is sql and I got NULLs for NBal

SELECT TranDate, (p.RunBal - c.Amt1 + c.Amt2 + c.Amt3) as NBal
FROM DayJournalTbl p, DayJournalTbl c
WHERE p.ID < c.ID

Thank you for helping.

 

[markros]

What is your SQL Server version?

I suggest to take a look at this thread

Lightning Fast Hybrid RUNNING TOTAL - Can you slow it down?
and adapt for your case.


PluralSight Learning Library

 

[OldSlowDog]

SQL 2005
Thanks