Click here to Skip to main content
14,545,215 members
Rate this:
Please Sign up or sign in to vote.
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
Rate this:
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
   
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:
Please Sign up or sign in to vote.

Solution 3

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

Solution 4

Not sure if this is still open. But you can try query like:

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
   
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
Rate this:
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
   
Comments
Member 13571003 17-Jul-18 7:46am
   
I don't understand,what's means previous Balance ?Please give me answer.
Rate this:
Please Sign up or sign in to vote.

Solution 5

The following code worked for me
it has the brought balance

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
   

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




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