Hi Everyone!
I don't know if I search the community enough but PLEASE help or advise on how to get the AVERAGE DAILY BALANCE in a transactional table.
For this I think I need 3 new fields in my table, kindly see below for the sample table with 1 customer record and the expected output in bold and red fonts
cust_no | trxn_date | balance | ADB_Date | ADB | Current_ADB |
10031 | 1-Jan-20 | 0 | 1-Jan-20 | 0 | 890.33 |
10031 | 2-Jan-20 | 100 | 2-Jan-20 | 50 | 890.33 |
10031 | 3-Jan-20 | 345 | 3-Jan-20 | 148.3 | 890.33 |
10031 | 4-Jan-20 | 50 | 4-Jan-20 | 123.75 | 890.33 |
10031 | 5-Jan-20 | 0 | 5-Jan-20 | 99 | 890.33 |
10031 | 6-Jan-20 | 0 | 6-Jan-20 | 82.5 | 890.33 |
10031 | 7-Jan-20 | 560 | 7-Jan-20 | 150.71 | 890.33 |
10031 | 8-Jan-20 | 400 | 8-Jan-20 | 181.88 | 890.33 |
10031 | 9-Jan-20 | 800 | 9-Jan-20 | 250.56 | 890.33 |
10031 | 10-Jan-20 | 1200 | 10-Jan-20 | 345.5 | 890.33 |
10031 | 11-Jan-20 | 1500 | 11-Jan-20 | 450.45 | 890.33 |
10031 | 12-Jan-20 | 1700 | 12-Jan-20 | 554.58 | 890.33 |
10031 | 13-Jan-20 | 0 | 13-Jan-20 | 511.92 | 890.33 |
10031 | 14-Jan-20 | 3200 | 14-Jan-20 | 703.93 | 890.33 |
10031 | 15-Jan-20 | 3500 | 15-Jan-20 | 890.33 | 890.33 |
I plan to do this in the script and guessing I could use peek here? but I am really in the dark on how to maximize that function with this requirement. Really appreciate your help! Thank you!