Differences

This shows you the differences between two versions of the page.

--- unbalanced_acc_trans [2016/10/27 09:48] – created sweitmann
+++ unbalanced_acc_trans [2024/09/25 06:21] (current) – external edit 127.0.0.1
@@ Line 1: / Line 1: @@
+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)
+   update acc_trans set amount = round(amount::numeric,2);