Traverse a tree Using SQL/ MLM Commissioning

Here is the simplified data table consisting of a id for the table itself, memberId, generated commission, ParentId, BranchId for the member, BranchId for the parent.

Few days ago, one of my friends had been asked about one impediment to me that he had gone through. He had rung several times to my cell and frantically searching me to do the job on behalf of him. Though I was busy with my stuffs, I didn’t make him disappoint. He somehow related with MLM concept (multilevel marketing) and wanted to generate the commission for all the members belongs to the company. The structure of participating members is almost like a binary tree. The company has several branches inside the country and collects data at the end of the day to a central database from many remote repositories. The hard part of the solution was that a parent can be from different branch for a child. However, I came up with a specific data table from merging many data table and found the solution in one SQL. The commission will have to be calculated based on the rule that is each parent will get 1 point for his all subordinate children.

alter proc mlmIncCom
@MemberId int,
@BranchId int

as

Declare @_MemberId int
Declare @_ParentId int
Declare @_BranchId int
Declare @_pBranchId int
Declare @_Comission decimal

if exists(select ParentId from test_Recursion where MemberId=@MemberId and BranchId=@BranchId)
begin
select @_ParentId=ParentId,@_pBranchId=pBranchId from test_Recursion where MemberId=@MemberId and BranchId=@BranchId
print Convert(varchar(10),@_ParentId)+ ': '+Convert(varchar(10),@_pBranchId)
select @_Comission=Commission from test_Recursion where MemberId=@_ParentId and BranchId=@_pBranchId
update test_Recursion set Commission=@_Comission+1 from test_Recursion where MemberId=@_ParentId and BranchId=@_pBranchId
print Convert(varchar(10),@_ParentId)+ ': '+Convert(varchar(10),@_Comission)

exec mlmIncCom @_ParentId,@_pBranchId
end


-- exec mlmIncCom 3,2


alter proc mlmTest
as

Declare @_MemberId int
Declare @_BranchId int

Declare Rec_Cursor cursor for select MemberId, BranchId from test_Recursion
Open Rec_Cursor
Fetch next from Rec_Cursor into @_MemberId, @_BranchId

while @@FETCH_STATUS = 0
begin
exec mlmIncCom @_MemberId,@_BranchId
Fetch next from Rec_Cursor into @_MemberId, @_BranchId
end
close Rec_Cursor
deallocate Rec_Cursor

-- exec mlmTest 1,1