Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Calculate the Running Total for the last five Transactions

By Divya Agrawal,

Introduction

This post is nothing new and it contains a very short and precise solution of a problem using CROSS JOINS. All of you might have worked with reports, which are generally an aggregation or calculation using some data. They could be even the calculation on calculated data. Let's take an example using EXCEL.

Problem

My problem is that I had an EXCEL file with three columns called ID, Date, and Balance. ID is an Identity column and Balance contains the balance deposited or debited on the given Date (from that column). I had to calculate the sum of the last five Transactions in an iterative way so that I had a computed column that will give me the Running Total. As far as EXCEL is concerned I have just written a formula as sum(C1:C5) where C1 to C5 are the balance of the last five Transactions. For the next row, the formula will be sum(C2:C6)... and this way it will continue on as I copy the formula to new cells. Looking at the Images below you will have a better idea of the results.

Here the RunningTotal is the computed column(column D) giving the sum of the last five transactions on an iterative level. The sum of Transaction ID's 1 to 5 is 510, sum of Transaction ID's 2 to 6 is 515, and so on. This is the same thing I had to develop in SQL Server, but in SQL Server you might be aware it is very difficult to add such a computed column that computes data on an iterative level. I have the option of using cursors or loops, but as you all know, it would degrade performance. So, I went for CROSS JOINS, which was a better option.

My Solution

First we will create a table name Accounts and insert some data into that table.

CREATE TABLE Accounts ( ID int IDENTITY(1,1), TransactionDate datetime, Balance float ) go insert into Accounts(TransactionDate,Balance) values ('1/1/2000',100) insert into Accounts(TransactionDate,Balance) values ('1/2/2000',101) insert into Accounts(TransactionDate,Balance) values ('1/3/2000',102) insert into Accounts(TransactionDate,Balance) values ('1/4/2000',103) insert into Accounts(TransactionDate,Balance) values ('1/5/2000',104) insert into Accounts(TransactionDate,Balance) values ('1/6/2000',105) insert into Accounts(TransactionDate,Balance) values ('1/7/2000',106) insert into Accounts(TransactionDate,Balance) values ('1/8/2000',107) insert into Accounts(TransactionDate,Balance) values ('1/9/2000',108) insert into Accounts(TransactionDate,Balance) values ('1/10/2000',109) insert into Accounts(TransactionDate,Balance) values ('1/11/2000',200) insert into Accounts(TransactionDate,Balance) values ('1/12/2000',201) insert into Accounts(TransactionDate,Balance) values ('1/13/2000',202) insert into Accounts(TransactionDate,Balance) values ('1/14/2000',203) insert into Accounts(TransactionDate,Balance) values ('1/15/2000',204) insert into Accounts(TransactionDate,Balance) values ('1/16/2000',205) insert into Accounts(TransactionDate,Balance) values ('1/17/2000',206) insert into Accounts(TransactionDate,Balance) values ('1/18/2000',207) insert into Accounts(TransactionDate,Balance) values ('1/19/2000',208) insert into Accounts(TransactionDate,Balance) values ('1/20/2000',209) go

By executing the script above, a table named Accounts will be created and 20 rows would be inserted. Now, to get the running balance after every five transactions I have used CROSS JOINS on the same table, which is explained below:

SELECT A.ID AS ID,B.ID AS BID, B.Balance FROM Accounts A cross JOIN Accounts B WHERE B.ID BETWEEN A.ID-4 AND A.ID AND A.ID>4

The query above is the innermost query, which will fetch every five Transaction ID's balance from table B for a given ID of table A on an iterative level where the alias names A and B has been given to the same table.

SELECT ID,sum(Balance) AS RunningTotal FROM (SELECT A.ID AS ID,B.ID AS BID, B.Balance FROM Accounts A cross JOIN Accounts B WHERE B.ID BETWEEN A.ID-4 AND A.ID AND A.ID>4 )T GROUP BY ID

The query above would group all the ID's and get the sum of the five transactions on an iterative level. The following query would help to display the whole output:

SELECT Acc.ID,CONVERT(varchar(50),TransactionDate,101) AS TransactionDate , Balance, isnull(RunningTotal,'') AS RunningTotal FROM Accounts Acc LEFT OUTER JOIN (SELECT ID,sum(Balance) AS RunningTotal FROM (SELECT A.ID AS ID,B.ID AS BID, B.Balance FROM Accounts A cross JOIN Accounts B WHERE B.ID BETWEEN A.ID-4 AND A.ID AND A.ID>4 )T GROUP BY ID ) Bal ON Acc.ID=Bal.ID

The outer join will give all the details of the table. By executing the query above you will get the following output:

It is also possible to calculate the running balance on a particular date just by adding in the group by clause. If you have any query or other good suggestions regarding this article put them in the discussion for this article.

Total article views: 4328 | Views in the last 30 days: 5
 
Related Articles
FORUM

Looping through the recordset to assign different balance value to each transaction row

This is to show row level balance infomration per transaction. So bascially i need to get end of tra...

ARTICLE

TSQL Challenge 76 - Identify the sequence of accounting transactions

The challenge is to identify the sequence of accounting transactions.

FORUM

Insert only one particular transaction?

Insert only one particular transaction?

FORUM

Inserting Markers in transactions in sqlserver 2000

Inserting Markers in transactions in sqlserver 2000

FORUM

Inserting Markers in transactions in sqlserver 2000

Inserting Markers in transactions in sqlserver 2000

Tags
cross join    
excel    
iterative    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones