Using SQL Cursor for Looping

If you ever heard of SQL cursor, this is probably not a new thing to you. If you never heard or use cursor in SQL before, we usually use cursor to replace the ordinary looping in SQL. The performance is generally better compared to using WHILE or ordinary looping method, depending on how it is implemented. Reason being, cursor is stored in memory.

I have a good example on usage of cursor in SQL.
In CRM & loyalty, we are looking at membership and loyalty points. Customers earn points when they transact or purchase something.
In this scenario, there is a points cap for members transactions, in which customers can only earn x number of points from their purchase.

We want to retrospectively calculate the correct points earned given customer's transactions and the points they earned.

Example of the records:

Points is capped at member-level. This means, different customer / member can have different points cap, denoted in the last column in the example above.
Since we are looking at member-level, we will look at the re-arranged version of the records.

This is the end result we are looking at. Observe the last field in the table. The content in the Correct_PointsEarned is always within the points cap for each member, in the order of the transaction sequence. Once the points cap has been met, the Correct_PointsEarned will be 0 for that member.

You should familiarize yourself with how to create a cursor first.
This is a template of how a cursor looks like:

--START CURSOR
declare cur1 cursor for
select *
from 
order by 

open cur1

fetch next from cur1
into @zzz, @sss, ......

while @@FETCH_STATUS = 0
BEGIN
 --do the necessary processing here
 
 
 --fetch next record
 fetch next from cur1
 into @zzz, @sss, ......
END

close cur1
deallocate cur1
--END CURSOR

This is the script to create temp table to populate the data:

create table #TmpPointsCap (
 TransactAutoID bigint identity(1,1),
 MemberID nvarchar(100),
 TransactDate date,
 TransactPoints numeric(18,4),
 PointsCap numeric(18,4),
 Correct_PointsEarned numeric(18,4) default(0.00),
 AddedOn datetime default(getdate())
)

insert into #TmpPointsCap (MemberID, TransactDate, TransactPoints, PointsCap)
values ('M0001', '2019/01/01', 100, 700)

insert into #TmpPointsCap (MemberID, TransactDate, TransactPoints, PointsCap)
values ('M0002', '2019/01/02', 200, 500)

insert into #TmpPointsCap (MemberID, TransactDate, TransactPoints, PointsCap)
values ('M0004', '2019/01/02', 400, 300)

insert into #TmpPointsCap (MemberID, TransactDate, TransactPoints, PointsCap)
values ('M0001', '2019/01/03', 500, 700)

insert into #TmpPointsCap (MemberID, TransactDate, TransactPoints, PointsCap)
values ('M0003', '2019/01/03', 700, 1000)

insert into #TmpPointsCap (MemberID, TransactDate, TransactPoints, PointsCap)
values ('M0001', '2019/01/05', 200, 700)

insert into #TmpPointsCap (MemberID, TransactDate, TransactPoints, PointsCap)
values ('M0002', '2019/01/06', 100, 500)

insert into #TmpPointsCap (MemberID, TransactDate, TransactPoints, PointsCap)
values ('M0003', '2019/01/06', 300, 1000)

insert into #TmpPointsCap (MemberID, TransactDate, TransactPoints, PointsCap)
values ('M0002', '2019/01/06', 400, 500)

insert into #TmpPointsCap (MemberID, TransactDate, TransactPoints, PointsCap)
values ('M0002', '2019/01/08', 200, 500)

insert into #TmpPointsCap (MemberID, TransactDate, TransactPoints, PointsCap)
values ('M0003', '2019/01/08', 100, 1000)

insert into #TmpPointsCap (MemberID, TransactDate, TransactPoints, PointsCap)
values ('M0004', '2019/01/09', 100, 300)

insert into #TmpPointsCap (MemberID, TransactDate, TransactPoints, PointsCap)
values ('M0005', '2019/01/10', 200, 200)

--Original table content
select * from #Tmppointscap

--The data sorted in the order we want
select TransactAutoID, MemberID, TransactDate, TransactPoints, PointsCap, Correct_PointsEarned = 0
from #TmpPointsCap
order by MemberID, TransactDate, AddedOn

And this is how we use cursor to achieve the correct point earned in the last column:

--Temporary variables to store the table fields
declare @MemberID nvarchar(100), @TransactAutoID bigint, @TransactPoints numeric(18,4), @PointsCap numeric(18,4)
declare @PrevMemberID nvarchar(100) = ''
declare @RunningPoints numeric(18,4) = 0


--START CURSOR
declare cur1 cursor for
select MemberID, TransactAutoID, TransactPoints, PointsCap
from #TmpPointsCap
order by MemberID, TransactDate, AddedOn

open cur1

fetch next from cur1
into @MemberID, @TransactAutoID, @TransactPoints, @PointsCap

while @@FETCH_STATUS = 0
BEGIN
 --do the necessary processing here
 if @MemberID <> @PrevMemberID
 begin
  --set initial running points as the 1st TransactPoints
  set @RunningPoints = 0
 end
 
 if @RunningPoints < @PointsCap and (@RunningPoints + @TransactPoints) > @PointsCap
 begin
  --if after adding the TransactPoints, it is bigger than the pointscap
  --only earn at most, as much as the pointscap
  update #TmpPointsCap
  set Correct_PointsEarned = @PointsCap - @RunningPoints
  where TransactAutoID = @TransactAutoID

  --Update RunningPoints = PointsCap
  set @RunningPoints += @TransactPoints
 end
 else if (@RunningPoints + @TransactPoints) = @PointsCap
 begin
  --earning all the transactpoints just good
  update #TmpPointsCap
  set Correct_PointsEarned = @TransactPoints
  where TransactAutoID = @TransactAutoID
  
  set @RunningPoints += @TransactPoints
 end
 else if @RunningPoints < @PointsCap
 begin
  --update the running points
  set @RunningPoints += @TransactPoints
  
  --update the correct points earned in the table
  update #TmpPointsCap
  set Correct_PointsEarned = TransactPoints
  where TransactAutoID = @TransactAutoID
 end
 else
 begin
  --not earning any points anymore
  update #TmpPointsCap
  set Correct_PointsEarned = 0
  where TransactAutoID = @TransactAutoID
 end
 
 set @PrevMemberID = @MemberID

 --fetch next record
 fetch next from cur1
 into @MemberID, @TransactAutoID, @TransactPoints, @PointsCap
END

close cur1
deallocate cur1
--END CURSOR

--Check the temp table again after updated
select TransactAutoID, MemberID, TransactDate, TransactPoints, PointsCap, Correct_PointsEarned
from #TmpPointsCap
order by MemberID, TransactDate, AddedOn

Good Luck, and Happy Query-ing!

Read More

Connecting to SQL Instance with Non-Default Port on Pentaho

If you are trying to connect to SQL instance on Pentaho, and your SQL instance is using non-default port, then you need to fill in the connection string a bit differently.

This is how we normally fill in the connection to SQL instance, when connecting to default port.

If you are connecting to some other ports, you need to explicitly type in the connection parameters inside the IP field.

Read More