Click here to Skip to main content
15,907,687 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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
Updated 22-May-20 19:15pm
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

Have a look here: http://www.tutorialspoint.com/mysql/mysql-sum-function.htm[^]

SQL
--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
 
Share this answer
 
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 ")"
 
Share this answer
 
Not sure if this is still open. But you can try query like:

SQL
select distinct 
c.retailer_id as 'retailer',
c.created as 'date',
sum(c.item_total)as 'sales_total',
COALESCE(sum(d.amount),0) as 'bill_received',
(sum(c.item_total)-COALESCE(sum(d.amount),0)) as 'balance'

from `retailer_inventory` c
left join `retailer_payment` d

on c.retailer_id = d.retailer_id
and c.created =d.payment_date

where c.retailer_id=17

group by c.retailer_id,date

order by c.retailer_id,date asc
 
Share this answer
 
Comments
Richard MacCutchan 1-Apr-18 3:39am    
FIVE years too late.
Member 13521806 1-Apr-18 9:26am    
I never hoped that problem would have been waiting for my solution for 5 years :). It is just that I was working on similar problem today. Tried google, got to this result. Then figure out solution and in expectation that it might help somebody someday, I posted it to complete answer for original question!!!
CHill60 2-Apr-18 17:08pm    
COALESCE(sum(d.amount),0) will return 0 if any value is null. Surely you just want to treat null values as zero, not the entire total.I.e.
sum(ISNULL(d.amount,0))
Member 10660969 31-Dec-18 2:48am    
Transaction Date Transaction Description Debit Credit balance BalType
--------------- ---------------------- ----- ----- -------- -------
01/04/2012 aintance charge 2800 .00 0.00 -2,800.00 Cr
01/04/2012 water charge 750.00 0.00 -3,550.00 Cr
16/04/2012 (...) 0.00 8,400.00 4,850.00 Dr
23/04/2013 (..) 0.00 8,400.00 13,250.00 Dr

Please hlp add New colom
Balance Type
if Balance Negative then Cr else Dr
SQL
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
 
Share this answer
 
Comments
Member 13571003 17-Jul-18 7:46am    
I don't understand,what's means previous Balance ?Please give me answer.
The following code worked for me
it has the brought balance

SQL
WITH tempDebitCredit AS (
Select 0 As Details_ID, null As Creation_Date, null As Reference_ID, 'Brought Forward' As Transaction_Kind, null As Amount_Debit, null As Amount_Credit, isNull(Sum(Amount_Debit - Amount_Credit), 0) 'diff'
From _YourTable_Name
where Account_ID = @Account_ID
And Creation_Date < @Query_Start_Date
Union All
SELECT a.Details_ID, a.Creation_Date, a.Reference_ID, a.Transaction_Kind, a.Amount_Debit, a.Amount_Credit, a.Amount_Debit - a.Amount_Credit 'diff'
FROM _YourTable_Name a
where Account_ID = @Account_ID
And Creation_Date >= @Query_Start_Date And Creation_Date <= @Query_End_Date
)

SELECT a.Details_ID, a.Creation_Date, a.Reference_ID, a.Transaction_Kind, 
a.Amount_Debit, a.Amount_Credit, SUM(b.diff) 'Balance'
FROM   tempDebitCredit a, tempDebitCredit b
WHERE b.Details_ID <= a.Details_ID
GROUP BY a.Details_ID, a.Creation_Date, a.Reference_ID, a.Transaction_Kind, 
a.Amount_Debit, a.Amount_Credit
Order By a.Details_ID Desc


Tested on Microsoft SQL Server
 
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