I'm working with accounting pack in java and i wanna generate reports just using queries without java codes.
I wrote a stored procedure to generate a result table using many data tables.
it has 5 columns. name, date, credit, debit and balance.
I wrote a query to fill data from other tables. But i couldn't calculate balance from credit and debit.
Balance = last balance + debit - credit;
columns should be "credit", "debit" and "balance".
eg :-
CREDIT, DEBIT, BALANCE
100 ,0 , 0
50 ,0 , 0
0 ,200 , 0
120 ,0 , 0
100 ,0 , 0
0 ,100 , 0
0 ,100 , 0
After process table row values should be
CREDIT, DEBIT, BALANCE
100 ,0 , -100
50 ,0 , -150
0 ,200 , 50
120 ,0 , -70
100 ,0 , -170
0 ,100 , -70
0 ,100 , 30
Thank you!
解决方案I have tried this based on the example in my comments...
update people set balance = (select newbalance from (select (balance + debit) - credit as newbalance from people));
I have assumed a people's table. I have used the example in my comments and it updates the table with 350; Please play around and see what you come up with.
Hope it helps.
I Found a solution. I could write a stored procedure for it!
And also i added another primary key field named "id".
CREATE PROCEDURE ManageCreditorBalance()
BEGIN
declare a_id int;
declare a_val double default 0;
declare a_balance double default 0;
declare done int default 0;
declare cur1 cursor for select id, credit*-1 as val from creditors where debit = 0
union all
select id, debit as val from creditors where credit = 0;
declare continue handler for not found set done=1;
set a_balance = 0;
open cur1;
igmLoop: loop
fetch cur1 into a_id, a_val;
if done = 1 then leave igmLoop;
end if;
set a_balance = (a_balance + a_val);
update creditors set balance = a_balance where id = a_id;
end loop igmLoop;
close cur1;
END
And I found another solution from codeproject friends help.
This is it.
select a.addedDateTime, a.credit, a.debit, a.balance from creditors a limit 1
union all
select a.addedDateTime, a.credit, a.debit, b.balance + a.debit - a.credit as balance
from creditors a inner join creditors b
on a.id - 1 = b.id