Monthly account balance

  • Thread starter Thread starter Frank Sung
  • Start date Start date
F

Frank Sung

Hi,

How to write a SQL query to get account's monthly balances?

A table looks like below.

Account Date Trans. Int. Rate
======= ======== ======== ========
0001 01/01/04 $100 3%
0001 02/01/04 $0 2%
0001 03/01/04 $50 1%
0001 04/01/04 -$100 2%
0001 05/01/04 $0 1%
0002 01/01/04 $200 3%
0002 02/01/04 -$50 2%
0002 03/01/04 $0 1%
0002 04/01/04 $50 2%
0002 05/01/04 $0 1%

The query's result should look like below.

Account Date Beg Bal Trans. End Balance
======= ======== ======== ======== ========
0001 01/01/04 $0.00 $100.00 $103.00 =(0+100)*(1+3%)
0001 02/01/04 $103.00 $0.00 $105.06 =(103+0)*(1+2%)
0001 03/01/04 $105.06 $50.00 $156.61 =(105.06+50)*(1+1%)
0001 04/01/04 $156.61 -$100.00 $57.74 =(156.61-100)*(1+2%)
0001 05/01/04 $57.74 $0.00 $58.32 =(57.74+0)*(1+1%)
0002 01/01/04 $0.00 $200.00 $206.00 =(0+200)*(1+3%)
0002 02/01/04 $206.00 -$50.00 $159.12 =(206-50)*(1+2%)
0002 03/01/04 $159.12 $0.00 $160.71 =(159.12+0)*(1+1%)
0002 04/01/04 $160.71 $50.00 $214.92 =(160.71+50)*(1+2%)
0002 05/01/04 $214.92 $0.00 $217.07 =(214.92+0)*(1+1%)

Beginning balance is last month's ending balance.
Ending balance = (Beginning balance + Transaction) * (1 + Interest Rate)

Thank you in advance.

Frank
 
Frank

What you've described sounds a lot like what Excel "does for a living". Is
there a business need to keep this in Access, or could you extract the data
to Excel and process there.
 
Jeff,

Thank you for your quick response.

Yes, it is working like Excel. However, our data are stored in SQL
server database. I think it may not be a good way to use Excel and
VBA to run the data.

I have no problem to write a running total query.

key value
=== =====
1 10
2 5
3 10
4 20
5 30

SELECT t1.key, t1.value, SUM(t2.value)
FROM t AS t1, t AS t2
WHERE t1.key >= t2.key
GROUP BY t1.key, t1.value

key value running sum
=== ===== ===========
1 10 10
2 5 15
3 10 25
4 20 45
5 30 75

I know how to write a cumulative product query.

key value running multiply
=== ===== ===========
1 10 10
2 5 50 (=10*5)
3 10 500 (=10*5*10)
4 20 10000 (=10*5*10*20)
5 30 300000 (=10*5*10*20*30)

SELECT t1.key, t1.value, EXP(SUM(LOG([t2.value])))
FROM t AS t1, t AS t2
WHERE t1.key >= t2.key
GROUP BY t1.key, t1.value

(values are not 0 or negative)

I know I can use stored procedures to make it work. If it is
possible, I am looking for such queries to get my monthly account
balanaces.

Thanks,

Frank
 
Back
Top