to get the average balance of an account
851505Apr 8 2011 — edited Apr 14 2011hi all :) ,
I have the following table "account_details"
account balance transaction_date
1 100 1/1/2011
1 200 5/1/2011
1 -100 20/1/2011
1 500 31/1/2011
1 200 3/3/2011
2 100 6/2/2011
account -> account number
balance -> the balance for that account
transaction_date -> date of transaction made.
I am required to calculate the average balance for every account for every month of the current year considering only the positive balances only. Phew! trying to make it simpler....
ex: account =1
avg balance ( for jan )= [ 100 * (5-1+1) + 200 * (31-5+11) + 500 * (31-31+1) ] /31
(please see that -100 has been ignored )
avg balance (for feb) = [500 * 28 ] / 28
i need to display the account number, the month and year and the average balance for that month.... [for all the previous 12 months from todays date]
Thanks in advance for all the geniuses out there :)
P.S i am using oracle 10 g
Edited by: user12288152 on Apr 8, 2011 12:51 AM