asked on

MYSQL Get Preveious Balnce

Hello I have query to clacultae the balacy of customers in my SQL
I want The Previous Balance to be added to current transaction
Result would look like : Balance = Sale+Hadnling+Freight+PreviousBalance .
This is My query:
SELECT
jklnzhang.id,
jklnzhang.Customer,

jklnzhang.SaleAmount,
jklnzhang.Handling,
jklnzhang.Freight,

jklnzhang.SaleAmount + jklnzhang.Handling+jklnzhang.Freight+ifnull(totaldepnsebycalno.Depense,0) AS Balance,
jklnzhang.Editor,
CustomerBalance.Balance AS Reall,
jklnzhang.dasuanzhanghao AS CBM,
TransfertTotalByCalNo.IcomeAmount,
jklnzhang.SaleAmount + jklnzhang.Handling+jklnzhang.Freight+ifnull(totaldepnsebycalno.Depense,0) -IFNULL(TransfertTotalByCalNo.IcomeAmount,0) AS OtherThing
FROM jklnzhang
LEFT OUTER JOIN CustomerBalance ON jklnzhang.Customer = CustomerBalance.CustId
LEFT OUTER JOIN TransfertTotalByCalNo ON jklnzhang.CalNo = TransfertTotalByCalNo.CalNo
LEFT OUTER JOIN totaldepnsebycalno ON jklnzhang.CalNo = totaldepnsebycalno.CalNo
ORDER BY jklnzhang.Customer

arnold

Previousbalance is best if maintained,calculated just before a change or on creation of entry.
Everything depends on your db structure.
You seem to want to tally, calculate prior balance on demand, versus having current balance, prior balance maintained by other means that would be preserved for historic reference, I.e. What was the balance and prior balance on or around date A.
Presumably you have a transaction table which records purchases, and payments.
On revord insertion, a trigger can fire that uses the current payment, purchase to update the prior balance to reflect the current balance prior to the purchase/payment and then update the current balance by adding, subtracting the amount from today's transaction as applicable.
Update balance_maintaining_table set priorbalance=Currentbalance,currentbalance=currentbalance+(@datafromtrigger) where customer=(@triggercustomer)

....

You have to look at how Accounting dictates this .....
I.e. Monthly record of either the Balance at the begining of the month, or at the end ........

This question needs an answer!

Become an EE member today

7 DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform.

View membership options

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.