positive and negative signs

i have account_entries table which has the following fields:

debit_or_credit

entry_amount

and I want when inserting every field to check if the inserted row was assigned to (D)ebit and (C)redit it should change the entry_amount accoridanly.

maybe something like trigger? plz help...

Are you sure you want to change the value? It could be better to have a calculated field like "case when debit_or_credit = 'D' then entry_amount else -entry_amount end as effective_amount"

Rob|||

I think it's better to keep the credit as - and debit as + in the database otherwise i have to place check routines everwhere, all screen and reports..

|||

A trigger could be written, but I would not personally want to do it that way. I would prefer to either do what Rob suggested or have a calculated column that calculated whether it was a debit or a credit. :

create table stuff
(
value decimal(10,2),
debit_or_credit as (case when value >= 0 then 'Credit' else 'Debit' end)
)
go
insert into stuff (value)
select 1.00
union all
select 2.00
union all
select -2.00
union all
select -100.00
go
select *
from stuff
go
Returns:

value debit_or_credit
- -
1.00 Credit
2.00 Credit
-2.00 Debit
-100.00 Debit

This way NO chance of invalid data...

|||But don't you actually want:

create table stuff
(
entry_amount decimal(10,2),
debit_or_credit char(1),
effective_value as (case when debit_or_credit = 'D' then entry_amount else -entry_amount end)
);
go
insert into stuff values (50,'D');
insert into stuff values (50,'C');
select * from stuff;
/* Results */
entry_amount debit_or_credit effective_value

50.00 D 50.00
50.00 C -50.00

Then you can sum up 'effective_value' (restricting to date-ranges, or whatever) to see the net income/loss. And of course, there are times when you may have a negative entry_amount (for example, if you have an adjustment of depreciation). That should be counted as negative in entry_amount, but could translate to a positive amount in effective_value.

Either way though, a calculated field is the way to go, as I hope Louis and my examples have shown.

Rob|||

i have one more non-IT question plz..

I have assigned the visit fees as expense, visit payment as income

what about the discount? will be positive or negative? income or expense?

|||I'm hoping I've answered your question correctly - I'm not entirely sure of the situation. Here I'm assuming you have revenue which is paid to you, but some people may have a discount, which reduces how much you charge them.

So it depends on whether you count discount as an expense, or a reduction in revenue. I think you should do it as an expense, so that you can clearly see what your discount expense is - but other people might do that differently.

And yes, this is an area where you could potentially have a negative amount.

Rob