Differences

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

--- compare_amount_paid_in_ar_and_acc_trans [2016/09/19 13:33] – created sweitmann
+++ compare_amount_paid_in_ar_and_acc_trans [2016/09/19 13:39] – sweitmann
@@ Line 1: / Line 1: @@
-   SELECT ar.id, invnumber, ar.amount, (SELECT SUM(amount) FROM acc_trans ac WHERE ac.trans_id = ar.id AND ac.chart_id IN (SELECT id FROM chart WHERE link NOT LIKE '%_paid%')) amount2 FROM ar WHERE paid <> 0 AND id IN (SELECT trans_id FROM acc_trans GROUP BY trans_id HAVING SUM(amount) = 0) GROUP BY 1,2,3;
+First check the AR amount without payment:
+   SELECT SUM(amount) FROM acc_trans WHERE trans_id = 'xxxxx' and chart_id in (select id from chart where link not like '%_paid%');
+Then check payment amount:
+    SELECT SUM(amount) FROM acc_trans WHERE trans_id = 'xxxxx' and chart_id in (select id from chart where link like '%_paid%');
+Or run a query to compare all values from the two tables:
+     SELECT ar.id, invnumber, ar.amount, (SELECT SUM(amount) FROM acc_trans ac WHERE ac.trans_id = ar.id AND ac.chart_id IN (SELECT id FROM chart WHERE link NOT LIKE '%_paid%')) amount2 FROM ar WHERE paid <> 0 AND id IN (SELECT trans_id FROM acc_trans GROUP BY trans_id HAVING SUM(amount) = 0) GROUP BY 1,2,3;