Re: Accounting ERD
From: Kenneth Downs <MyUseNetHandle_at_linuxmail.org>
Date: Sun, 27 Apr 2003 20:53:25 -0400
Message-ID: <qeuh8b.iod.ln_at_mercury.downsfam.net>
> Agreed. And it prompts me to share with you the egregious design I have
> to work with, which has 2 columns: amount and type, where type is
> effectively 'CREDIT' or 'DEBIT' and amount is always +ve. So to get the
> total you have to do something like:
>
> SUM( CASE WHEN type='CREDIT' THEN amount ELSE -amount END ) FROM Ledger;
>
> Horrible!
>
> --
> Posted via http://dbforums.com
Date: Sun, 27 Apr 2003 20:53:25 -0400
Message-ID: <qeuh8b.iod.ln_at_mercury.downsfam.net>
Quoting unnamed sources, andrewst claimed:
>
> Originally posted by Kenneth Downs One column of
> numbers is simpler. Consider the most basic check of all,
>> which is to see if it balances. You can do: >> >> SELECT SUM(trxamt) from Ledger >> >> Or you can do: >> >> SELECT SUM(debits) - SUM(credits) FROM Ledger >> >> While there is little difference between those two expressions, >> every small >> complication introduced at the beginning will multiply throughout the >> entire system. Better to squeeze for every measure of >> simplicity in the >> beginning and have a more manageable system when it gets larger. >> >>
> Agreed. And it prompts me to share with you the egregious design I have
> to work with, which has 2 columns: amount and type, where type is
> effectively 'CREDIT' or 'DEBIT' and amount is always +ve. So to get the
> total you have to do something like:
>
> SUM( CASE WHEN type='CREDIT' THEN amount ELSE -amount END ) FROM Ledger;
>
> Horrible!
>
> --
> Posted via http://dbforums.com
Hmmm, can you do a one-time replace of the type column with either a plus one or minus one, and then do an unconditional multiplication:
SELECT type*amount as "trxamt" ...
If you control the input, or can gain control of it through an update trigger, this perhaps can be made permanent.
-- Kenneth DownsReceived on Mon Apr 28 2003 - 02:53:25 CEST