Click here to Skip to main content
12,504,695 members (56,660 online)
Rate this:
 
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 21:54pm
Updated 11-Feb-13 22:16pm
v3
Comments
Irbaz Haider Hashmi 12-Feb-13 4:00am
   
What have you done so far. Provide your work.
Also provide the table structure.
aravindnass 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 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 14-Feb-13 5:18am
   
Have a look at the last example...
aravindnass 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 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)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.160919.1 | Last Updated 11 Nov 2014
Copyright © CodeProject, 1999-2016
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