On Fri, Jul 22, 2011 at 11:24 AM, Rich Shepard <[email protected]> wrote: > On Fri, 22 Jul 2011, Chris Travers wrote: > >> First, there are other cases where fractional cents may be tracked >> (for example sales tax liabilities) but they shouldn't affect bank >> deposits or withdrawals. > > Chris, > > Then the decimal part of the numeric value can be expanded to 3 or 4 > digits. I remember when postgres dropped the MONETARY alias to the NUMERIC > data type, but I used the latter anyway. > >> What I'd recommend doing first is just transforming the field to an >> unbounded numeric type I.e. without parentheses. This represents the >> money in PostgreSQL's arbitrary precision math form (internally a string >> of 10-bit units, each representing an integer from one to 1000. Why 1000? >> Because it's more efficient to lose 24 bits per 1024 to overhead than 6 >> per 16). >> >> You can do this by (pg 8.1 and above): >> >> ALTER TABLE acc_trans ALTER COLUMN amount TYPE numeric; > >> That may not fix your problem though. > > No, but it's worth doing ... done. > >> The first question what does SQL-Ledger think your balance should be? > > Where? In the trial balance it's correct. When reconciling it's not > correct.
Ok. This is a place to start. Here are the queries I'd be looking at to start: SELECT sum(amount) FROM acc_trans WHERE chart_id IN (select id from chart where accno = '[...]') AND cleared is true; SELECT sum(amount) FROM acc_trans WHERE chart_id IN (select id from chart where accno = '[...]') AND cleared is true and transdate <= '[ballance_date]'; SELECT sum(amount) FROM acc_trans WHERE chart_id IN (select id from chart where accno = '[...]') AND cleared is true and transdate >= '[ballance_date]'; SELECT count(*) FROM acc_trans WHERE chart_id IN (select id from chart where accno = '[...]') UNION SELECT count(*) FROM acc_trans WHERE chart_id IN (select id from chart where accno = '[...]') AND trans_id IN (SELECT id FROM ar UNION select id from ap UNION select id from gl); On the latter, a not in query will give you the same info but if your db is large that can take a while. Instead if the two numbers don't match you have an issue issue. Best Wishes, Chris Travers _______________________________________________ SQL-Ledger mailing list [email protected] http://lists.ledger123.com/mailman/listinfo/sql-ledger
