User Tools

Site Tools


compare_amount_paid_in_ar_and_acc_trans

Differences

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

Link to this comparison view

Both sides previous revision Previous revision
compare_amount_paid_in_ar_and_acc_trans [2016/09/19 13:39]
sweitmann
compare_amount_paid_in_ar_and_acc_trans [2016/09/20 12:35] (current)
sweitmann
Line 1: Line 1:
 First check the AR amount without payment: 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%'​);​+   ​SELECT SUM(amount) FROM acc_trans WHERE trans_id = ID and chart_id in (select id from chart where link not like '​%_paid%'​);​
   ​   ​
 Then check payment amount: 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%'​);​+    SELECT SUM(amount) FROM acc_trans WHERE trans_id = ID and chart_id in (select id from chart where link like '​%_paid%'​);​
     ​     ​
 Or run a query to compare all values from the two tables: 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;      ​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;
 +     
 +Another query to check if transaction is completely balanced:
 +
 +    SELECT SUM(amount) FROM acc_trans WHERE trans_id = ID
compare_amount_paid_in_ar_and_acc_trans.txt ยท Last modified: 2016/09/20 12:35 by sweitmann