[SQL-Ledger-users] OT: Web-based Checking Software

cbbrowne@hex.net cbbrowne@hex.net
Sun, 06 May 2001 00:00:03 -0500


On Sat, 05 May 2001 23:17:14 CDT, the world broke into rejoicing as
linas@linas.org (Linas Vepstas)  said:
> On Sat, May 05, 2001 at 04:17:40PM -0500, Donald L Greer Jr. was heard to rem
ark:
> >   Linas,
> >   Rather than keep a running total on a per-transaction basis, why not
> > keep a daily balance.  Then if you change a transaction 2 months ago,
> > you've got to apply one change to 60 entries and the change is always
> > the same (+/- X).  
> 
> right.  This is what I called a 'balance checkpoint' in the original
> note. (could be daily, could be every N transactions, whatever).
> 
> > A single query can do that lickity-split.

> Umm. How good are you at sql?  I can't say that I know how to select
> 60 rows, add a value to a field, and update those rows, in a single
> sql statement.  Have I just not mastered the finer points of sql?
> Can you sketch out that statement?

The approach I'd take would be to build a "balance table," rebalancing
using something like the following:

--> Suppose our policy is to set up balances as at every month end.

--> We're using a table looking like SQL-Ledger's "acc_trans".

--> We just modified a transaction dated in March 2001, for account
    2100.

SQL queries that need to be run at this time:

   delete from total_tbl where account = 2100 and transdate =
       '2001-03-31';

   select 2100 as accno, sum(amount) as amount, '2001-03-31' as enddate
          from acc_trans
      into total_tbl
      where
          accno = 2100 and transdate <= '2001-03-31' 
          and transdate >= '2001-03-01';

This leaves off computing the totals for all time; it would then make
sense to do a query something like:

Q1 select enddate, amount from total_tbl where accno = 2100 
              and enddate >= '2001-03-01' order by enddate;

  and then iterate on each member of this set set thus:
Q2 select endbal from total_tbl where accno = 2100 and enddate =
     [date from Q1 - 1 month];

   update total_tbl
      set endbal = [endbal from Q2 + amount from Q1]
        where accno = 2100
        and enddate = [date from Q1];

There might be a clever way of cutting down on the number of queries
and updates; this certainly cuts the work down from "updating _EVERY_
transaction's balance."

> The problem with what you describe is this: Say I want to display
> all transactions since noon, but the last balance checkpoint was
> midnight.  Thus, I have to query all transactions since midnight,
> compute the running blance, and display only those since noon.  It
> requires a little extra work, but its doable.

There needs to be a "balance" here (using the word "balance" in a
completely different sense..).

One extreme would be to have the "balance table" be continuous, where
there's a balance for each transaction.  And so every modification to
data results in updating vast numbers of balance values, which is
expensive.

The other extreme is to basically do a DBMS query to continually
recalculate balances, also expensive.

> Now imagine I want to show all transactions whose memo field starts
> with the letter A...
> 
> see what the problem is? first I get all with letter A.  Then I need to
> figure out what checkpoint lies before the earliest transaction, get
> that, and then get all transactions since then, then compute the running
> balance ...  Ugh.

That sort of begs the question of what criteria will be used to order
the transactions.  The "sort by memo field" scheme seems unlikely to
me; transactions are likely primarily ordered under the hierarchies of
account, then date.

For memo field to be significant would require that particular
accounts have more than 30 transactions per day, which is rather a lot
of activity.  If there be that much activity, I think I'd want to
raise the number of transactions somewhat...
--
(reverse (concatenate 'string "gro.mca@" "enworbbc"))
http://vip.hyperusa.com/~cbbrowne/resume.html
"A  ROUGH  WHIMPER  OF   INSANITY"  is  an  anagram  for  "INFORMATION
SUPERHIGHWAY".