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!
