Use Of Self Joins Instead Of Cursor Or While Loop

Introduction

This is an example and I like it using everytime when I have such a requirement, i.e., to get the total of amount as you read the next record by moving up or down.

This example explains how you could get the running balance for an account using self joins.

Background

Earlier and even now, many people use the Cursor and while loop to get the balance of an account by date and their transaction.

Cursor, as we all know, takes time to execute and a cumbersome code syntax.

Which was eventually replaced by while loop, though it was a performance improvement as against cursor it has to go to each record and do the operation.

So here, we are going see the examples of each and self join.

Using the Code

Consider you have the table "Passbook" as below:

So to get the account balance by each transaction, we can use Cursor:

DECLARE @BalCursor float=0
DECLARE @TransctionNumber int
DECLARE @TransactionType Varchar(1)
DECLARE @TransactionAmount float

DECLARE @MyCursor CURSOR

SET @MyCursor = CURSOR 
FOR
SELECT TransctionNumber,TransactionType,TransactionAmount
FROM   #Passbook order by TransctionNumber

OPEN @MyCursor
    FETCH NEXT FROM @MyCursor
    INTO @TransctionNumber, @TransactionType, @TransactionAmount
        WHILE @@FETCH_STATUS = 0
        BEGIN
            select @BalCursor=@BalCursor+Case when @TransactionType='C' 
                       then @TransactionAmount else -@TransactionAmount end
            

            Update #Passbook set AccountBalance=@BalCursor 
                         where TransctionNumber=@TransctionNumber
             
            FETCH NEXT FROM @MyCursor
            INTO @TransctionNumber, @TransactionType, @TransactionAmount
        END
CLOSE @MyCursor
DEALLOCATE @MyCursor

select * from #Passbook

You will get output as shown below:

Now, you can do the same thing using While loop as below:

Update #Passbook set AccountBalance=0 

DECLARE @WhileBalance float
Declare @MinTransNo int,@MaxTransNo int
select @MinTransNo=MIN(TransctionNumber),@MaxTransNo=MAX(TransctionNumber) from #Passbook

While @MinTransNo<=@MaxTransNo
BEGIN

            select @WhileBalance=SUM(Case when p1.TransactionType='C' then P1.TransactionAmount else -P1.TransactionAmount end)
            From #Passbook p1 where TransctionNumber<=@MinTransNo

            Update #Passbook set AccountBalance=@WhileBalance where TransctionNumber=@MinTransNo
            
            set @MinTransNo=@MinTransNo+1

END

select * from #Passbook

and here comes the Self Join 

 Collapse | Copy Code

select
P2.TransctionNumber,P2.AccountHolderCode,P2.TransactionType,P2.TransactionDate,_
SUM(Case when p1.TransactionType='C' _
then P1.TransactionAmount else -P1.TransactionAmount end) AccountBalance from #Passbook P1
Inner Join 
#Passbook P2
On P1.AccountHolderCode=P2.AccountHolderCode and P1.TransctionNumber<=P2.TransctionNumber
Group By P2.TransctionNumber,P2.AccountHolderCode,P2.TransactionType,P2.TransactionDate

which can achieve the same output.

Also Some cool stuff things with below query and output as below

Ref :- http://www.codeproject.com/Tips/838497/Use-Of-Self-Joins-Instead-Of-Cursor-Or-While-Loop