<img alt="" src="https://secure.leadforensics.com/150446.png " style="display:none;">
Go to top icon

How to get the running balance from a table with a single SQL Query

Adel Merchant Mar 08, 2008

SQL running balance query Technology

 

Table structure for Balance table-

Required result from the query -

 

Like in a Bank's money transaction output, the RunningBalance column
is needed which displays the balance an account holds against that will account
transaction.

Query to achieve it -

Explanation -

The result is achieved by a self join of the Balances table. The first table b1 returns the complete table output, inner join has two conditions. The first condition 'b1.DateStamp >= b2.DateStamp' is the most important which gives the result for SUM(b2.Amount). The last two records for BalanceID 4 and 6 have the exact same DateStamp hence without the second condition 'b1.BalanceID >= b2.BalanceID' will give same result for the RunningBalance column.

e-Zest is a leading digital innovation partner for enterprises and technology companies that utilizes emerging technologies for creating engaging customers experiences. Being a customer-focused and technology-driven company, it always helps clients in crafting holistic business value for their software development efforts. It offers software development and consulting services for cloud computing, enterprise mobility, big data and analytics, user experience and digital commerce.