| Author |
Topic |
|
websyd
Starting Member
16 Posts |
 Posted - 2009-06-02 : 04:18:01
|
| Hi,I have this table called transactions. It has the following columns :TransDate DatetimePurchases MoneySales MoneyBalance MoneyI want to be calculating the Balance Columns automatically as records are inserted into either of the 2 columns (Purchases or sales). |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2009-06-02 : 04:19:57
|
what's the formula for Balance ?
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
websyd
Starting Member
16 Posts |
Posted - 2009-06-02 : 04:28:47
|
quote:
Originally posted by khtan
what's the formula for Balance ?
KH[spoiler]Time is always against us[/spoiler]
The formula for balance is :Balance = Balance + Sum(Purchases) - Sum(Sales) |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2009-06-02 : 04:35:38
|
quote:
Originally posted by websyd
quote:
Originally posted by khtan
what's the formula for Balance ?
KH[spoiler]Time is always against us[/spoiler]
The formula for balance is :Balance = Balance + Sum(Purchases) - Sum(Sales)
Why don't you do this in your query when you are selecting from the table rather than update it everytime whenever records are inserted / deleted from the table
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
websyd
Starting Member
16 Posts |
Posted - 2009-06-02 : 04:40:02
|
| Ok,Please can you show me a code snippet ? |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2009-06-02 : 04:44:00
|
[code]select product_id, balance = sum(purchase) - sum(sales)from transactionsgroup by product_id[/code]i assume you have a product_id in your table ?
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
websyd
Starting Member
16 Posts |
Posted - 2009-06-02 : 05:15:34
|
| This code does not work please kindly check the table and see if u can just cook something for me. |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2009-06-02 : 05:22:46
|
quote:
Originally posted by websyd
This code does not work please kindly check the table and see if u can just cook something for me.
The comments is not clear please kindly rephrase and see if you provide more information.
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
websyd
Starting Member
16 Posts |
Posted - 2009-06-02 : 06:22:00
|
| Khtan,Im storing the following info's : TransactionDate, InvoiceAmount, RecieptAmount, Balance. InvoiceAmount is the Debit while RecieptAmount is Credit. What i want is that each i insert either InvoiceAmount or RecieptAmount, the system should automatically calculate the balance and insert into the Balance column. |
|
|
|
tosscrosby
Aged Yak Warrior
676 Posts |
Posted - 2009-06-02 : 10:00:35
|
| Running totals shouldn't be stored in the database, as khtan has shown a simple, workable solution to that. Can you provide DDL, sample data and the results you would expect to be returned?Terry-- Procrastinate now! |
|
|
|
|
Balance Column