Click here to Skip to main content
15,867,568 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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!
Posted
Updated 5-May-15 16:34pm
v3
Comments
Anele 'Mashy' Mbanga 5-May-15 7:51am    
HiJust for clarity so that I can help you, your "pos" table has only two columns a and b or it has more? I'm trying to understand why your second select statement is "select *" as this selects all fields, why don't you just work with the columns you want?
Member 10361041 5-May-15 8:07am    
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;

But finally i could to write stored procedure for it.

Thank you!

Have you another solution?
Anele 'Mashy' Mbanga 5-May-15 10:26am    
Ohk, I get you. So the "last balance" from your formular above is the balance that is already there? If I understand this correctly, if for example we currently have a credit = 300, a debit = 50 and a balance = 600, you want your result to be 600 + 50 - 300 = 350 right?
Anele 'Mashy' Mbanga 5-May-15 13:03pm    
Hi again, I think you might have to consider using a trigger with your example within the database itself. I have also found out that you can also perhaps use a self join which I think was what you were trying to achieve. Whilst the example below is not what you want, perhaps its something you can explore for more details.

http://www.mysqltutorial.org/mysql-tips/mysql-compare-calculate-difference-successive-rows/

I will keep on looking for something for you.

Please also update your questions in the future to provide as much details as possible, as usually a process flow is much better to understand what one wants to achieve. Will keep you posted...
Member 10361041 5-May-15 22:31pm    
ok, thanks! This is my first post in ever to ask a question. Anyway Thank you sooo much!

I tried to update my table. But i didn't look about select. This is what i searched. Thank you again.

http://www.mysqltutorial.org/mysql-tips/mysql-compare-calculate-difference-successive-rows/

And also i improve it for a my result.

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

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.
 
Share this answer
 
Comments
Member 10361041 5-May-15 11:16am    
Yes, u get it correctly! But your query is not works fine!
If your can please try it with a table and some rows.(3 or more rows)
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
Anele 'Mashy' Mbanga 5-May-15 12:32pm    
The balances calculated here are just for 1 account isnt it?
Member 10361041 5-May-15 12:50pm    
It's optional! it isn't depend on number of accounts. But if you can do it for one account we can improve it for all.
I found a solution using stored procedure. But if can give a single query for it, it's very useful!
Thank you!
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
 
Share this answer
 
v2
Comments
Anele 'Mashy' Mbanga 5-May-15 13:23pm    
Wow, I have checked your example and if it works, then all is good. Remember, there are multiple records for your example that have different ids, or primary keys and to achieve your required output, your procedure above uses a loop for each record. Happy coding... and all the best
Member 10361041 5-May-15 22:16pm    
Oh, sorry! I forgot to tell you I add another column which includes auto increment primary key. That's what i used as "id" in my procedure query.

That table also generating via procedure. Then i can edit it also!
Thank you!
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
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900