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.