SF.net SVN: ledger-smb:[3105] addons/1.3/enhanced_tb/trunk/sql/modules/ trial_balance.sql

[..hidden..]

Revision: 3105
          http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=3105&view=rev
Author:   einhverfr
Date:     2010-10-25 21:39:37 +0000 (Mon, 25 Oct 2010)

Log Message:
-----------
Correcting join exclusion issues which cause some accounts to drop off.  This may require a couple minor changes to the trial balance work flow regarding displaying unused accounts, however

Modified Paths:
--------------
    addons/1.3/enhanced_tb/trunk/sql/modules/trial_balance.sql

Modified: addons/1.3/enhanced_tb/trunk/sql/modules/trial_balance.sql
===================================================================
--- addons/1.3/enhanced_tb/trunk/sql/modules/trial_balance.sql	2010-10-21 22:54:55 UTC (rev 3104)
+++ addons/1.3/enhanced_tb/trunk/sql/modules/trial_balance.sql	2010-10-25 21:39:37 UTC (rev 3105)
@@ -101,7 +101,7 @@
 			-- between the start checkpoint and the start_date.
 			-- Most conditions are for the case of either start or the
 			-- checkpoint missing.
-			COALESCE(cpb.debits, 0) +
+			COALESCE(abs(cpb.debits), 0) +
 			COALESCE(SUM(CASE WHEN COALESCE(ac.amount, 1) > 0 THEN 0
 			ELSE
 			    CASE WHEN (cpb.end_date IS NOT NULL
@@ -157,6 +157,14 @@
 		LEFT JOIN 
 		(SELECT ac.chart_id, ac.trans_id, ac.amount, ac.transdate 
     	 FROM acc_trans ac
+     	 JOIN -- Moving this join into the inline view so as to avoid
+             -- inner vs left join problems 
+     	(SELECT id, approved FROM ar
+ 		 UNION
+         SELECT id, approved FROM gl
+         UNION
+         SELECT id, approved FROM ap) gl 
+        ON (ac.trans_id = gl.id AND gl.approved IS TRUE)
     	 WHERE ac.approved IS TRUE AND transdate <= date_to AND
     	 ((cpa_date IS NULL AND cpb_date IS NOT NULL AND 
      	 (ac.transdate < date_from OR ac.transdate > cpb_date)) OR
@@ -166,13 +174,6 @@
      	 (cpa_date IS NULL AND cpb_date IS NULL) OR 
      	 (cpa_date IS NOT NULL AND cpb_date IS NULL AND 
      	 ac.transdate > cpa_date))) ac ON  (ac.chart_id = a.id)
-     	JOIN 
-     	(SELECT id, approved FROM ar
- 		 UNION
-         SELECT id, approved FROM gl
-         UNION
-         SELECT id, approved FROM ap) gl 
-        ON (ac.trans_id = gl.id AND gl.approved IS TRUE)
 		LEFT JOIN (SELECT account_id, amount, end_date, debits, credits
                      FROM account_checkpoint 
                     WHERE end_date = cpa_date) cpa


This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.