Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2014

    Post Unanswered: How to update data automatically

    Hi, I'm completely new to database. Wish I can find my answer here.

    From the attachment(image), there is 3 fields, payment, expenses and balance.

    Here's the scenario, my customer has bought the ticket for $50, and spent $27.17, so he had the balance of $22.83.

    During the next transaction, he had reloaded $30.00 into his account and spent $29.26 and the balance for second transaction is $0.74 which doesn't make sense at all since his account has $22.83 for the previous transaction.

    So, is there any method to add up balance(first transaction) with payment(second transaction) then only we minus the expenses(second transaction).

    Pretty easy in programming but I found it so hard in database, please help!
    Attached Thumbnails Attached Thumbnails photo.JPG  

  2. #2
    Join Date
    Aug 2014

    Post How to update data automatically

    Hi guys I'm new to database and would like to ask a question.

    Here's the scenario, I'm currently running a private taxi center business, my customer has reloaded $50.00 into his account for his 1st transaction and spent $27.17, so he has $22.83 as balance.

    on the 2nd transaction he had topped up $30.00 into his account and spent $29.26, $0.74 as the balance which doesn't make sense at all because he still has $22.83 as the balance for first transaction.

    So, to calculate the balance for the 2nd transaction, the database will need to add balance(1st transaction) and payment(2nd transaction) then only minus expenses(2nd transaction).

    How do I achieve this? Please help!
    Attached Thumbnails Attached Thumbnails photo.JPG  

  3. #3
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    in a normalised database you don't calculate the balance, thats derived data and as such should be calculated as and when required, UNLESS you have good reasons to do so.

    storing derived data is not a smart call. why, well what happens in your proposed model if someone edits the balance, without realising what they are doing. that then throws the calculation off for every subsequent calculation

    to calculate a balance use the following query
    select sum(payment) - sum(Expenses) as balance from mytable

    if you go down your current route then you would also need to know when a payment was made

    update mytable as t1
    set t1.balance = t1.payment - t1.expenses +
    ( select top 1 t2.balance from mytable as T2
    where t2.transactiontimestamp < t1.transactiontimestamp
    order by t1.transactiontimestamp)
    I'd rather be riding on the Tiger 800 or the Norton

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts