Here
is a perl script which parses a text delimeted account statement and generates
an SQL script that gives average balance maintained for the statement period.
If the statement is for a quarter, it gives average quarterly balance (AQB).
#!/usr/bin/perl
# Usage: perl gen_aqb_calc.pl < stmt_file.txt > aqb_calc.sql
$seq = 1;
print "set
feedback off;\n\n";
print "create
table xcts(sn number, dt date, bal number(20,2));\n\n";
while(<stdin>)
{
next unless /^\s*\d\d\/\d\d\/\d\d/; # Skip heading and blank lines
@fields = split(',');
# Example format of downloaded
statement is
# 20/10/11 ,POS 4214 BHARTI AIRTEL LI ,20/10/11 , 444.00 , 0.00 ,1011413 , 23730.59
$date = $fields[0];
$closing_bal = $fields[6];
$date =~ s/\s//g;
$closing_bal =~ s/\s//g;
print "insert into xcts values ($seq, to_date('$date',
'dd/mm/yy'), $closing_bal); \n";
$seq++;
}
print "\ncommit;\n";
print "
with closing_balances as(select x1.dt, x1.bal
from xcts
x1,
(select max(sn) maxsn, dt from xcts group by dt) x2
where x1.sn
= x2.maxsn
order by
x1.dt)
select round( sum(t1.bal *
nvl(t2.dt - t1.dt, 1)) / (max(t1.dt) - min(t1.dt) + 1) , 2 ) average_balance
from (select rownum rn, dt,
bal
from
closing_balances) t1,
(select rownum - 1 rn,
dt
from
closing_balances) t2
where t1.rn = t2.rn(+);
";
print "\ndrop
table xcts purge;\n";
The
perl script generates below SQL script. When we execute it against Oracle
database, it gives average balance.
set feedback off;
create table xcts(sn number, dt date, bal number(20,2));
insert into xcts values (1, to_date('01/10/11', 'dd/mm/yy'), 45651.08);
-- Some more insert statements here, removed from clarity
commit;
with closing_balances as(select x1.dt, x1.bal
from xcts x1,
(select max(sn) maxsn, dt from xcts group by dt) x2
where x1.sn = x2.maxsn
order by x1.dt)
select round( sum(t1.bal * nvl(t2.dt - t1.dt, 1)) / (max(t1.dt) - min(t1.dt) + 1) , 2 ) average_balance
from (select rownum rn, dt, bal
from closing_balances) t1,
(select rownum - 1 rn, dt
from closing_balances) t2
where t1.rn = t2.rn(+);
drop table xcts purge;
