Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: MySQL
haiii...
 
I am stuck with a query of Transaction statement (balance sheet )...
 
I want calculate the balance of credit and debit of a customer...
 

Can you help me to develop query for calculate the balance ..(in mysql) .here is the normal structure..
                                                                      [ opening Balance - 0.00 ]
 
Transaction Date      Transaction Description   Debit     Credit          balance
---------------      ----------------------    -----     -----            --------
01/04/2012          maintance charge          2800       0.00              -2,800.00
 
01/04/2012           water charge             750.00     0.00              -3,550.00
 
16/04/2012           (...)                     0.00      8,400.00           4,850.00     
 
23/04/2013           (..)                      0.00      8,400.00           13,250.00
 

 
plz give a query to find the balance....
 
I think the equation is [(Credit - Debit) + previous.Balance]
 
[edit]Code block added - OriginalGriff[/edit]
Posted 11-Feb-13 22:54pm
Edited 11-Feb-13 23:16pm
v3
Comments
Irbaz Haider Hashmi at 12-Feb-13 4:00am
   
What have you done so far. Provide your work.
Also provide the table structure.
aravindnass at 12-Feb-13 6:22am
   
I just tried but ..failed..here is my query
 
SELECT q.`credit`,q.`debit`
,@bal:=IF(@Id=q.`id`, @bal + (q.credit - q.debit), q.credit - q.debit) AS balance
FROM Transcation AS q
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Have a look here: http://www.tutorialspoint.com/mysql/mysql-sum-function.htm[^]
 
--total balance
SELECT (SUM(debit)*-1) + SUM(credit) AS TotalBalance
FROM Trasaction
 
--daily balance
SELECT TransDate, (SUM(debit)*-1) + SUM(credit) AS DailyBalance
FROM Trasaction
GROUP BY TransDate
 
--period of time balance
SELECT (SUM(debit)*-1) + SUM(credit) AS PeriodBalace
FROM Trasaction
WHERE TransDate BETWEEN CURDATE() AND ADDDATE(CURDATE() INTERVAL -30 DAY)
 
--customer balance
SELECT CustId, (SUM(debit)*-1) + SUM(credit) AS CustomerBalance
FROM Trasaction
GROUP BY CustId
  Permalink  
v2
Comments
aravindnass at 14-Feb-13 5:15am
   
Thank you for your answer ..But i need Separate Balance of each customer...
I think the equation is [(Credit - Debit) + previous.Balance] to find ..but I am unable to write the query... plz help me...
Maciej Los at 14-Feb-13 5:18am
   
Have a look at the last example...
aravindnass at 14-Feb-13 5:53am
   
getting error here:
Error Code: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') AS DailyBalace
FROM Trasaction
GROUP BY CustId
LIMIT 0, 1000' at line 1
Maciej Los at 14-Feb-13 5:56am
   
Sorry, remove last ")"
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

you will get the last Balance by using this query
 
SELECT balance FROM Table name ORDER  BY date DESC LIMIT  1
 
Balance = (last Previous Balance + Credit) - Debit
 
than your query for insertion
  Permalink  

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



Advertise | Privacy | Mobile
Web02 | 2.8.150302.1 | Last Updated 11 Nov 2014
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100