International SQL-Ledger Network Association

Check total amount in acc_trans table

 select sum(amount) from acc_trans;

Show transactions with > 0.005 rounding difference

 select trans_id, sum(amount) from acc_trans where transdate between '2015-01-01 00:00' and '2016-01-01' group by trans_id having sum(amount) > 0.005;

Show transactions with > -0.005 rounding difference

  select trans_id, sum(amount) from acc_trans where transdate between '2015-01-01 00:00' and '2016-01-01' group by trans_id having sum(amount) < -0.005
 

Check total debit amount in acc_trans table

  select sum(amount) from acc_trans where amount > 0;

Check total credit amount in acc_trans table (should be the same as debit amount)

  select sum(amount) from acc_trans where amount <= 0;    
 

Only run this on backup database for testing:

 update acc_trans set amount = round(amount::numeric,2);