Thanks for your great article.:-)
I have a problem with three “While”-loops and was searching for a better solution.
Instead of using the “The problematic solution” now I use your “Set-based Loop”. Thanks for bringing this Idea back in my mind.:-)
But I have some trouble with your article.
First Point: (it deals with the comment from sam.dahl (old Hand))
select COUNT ([Level]), MemberId, GroupId from GroupsHistory
group by MemberId, GroupId
order by 1 desc
I got some combinations MemberID, GroupId, where Count was greater 1. I select one of them to understand your code.
select * from GroupsHistory where GroupId=248498 and memberid=2
select * from GroupsHistory where PreviousGroupId=248498 and PreviousMemberId=2
If I have a client with MemberID=6 and GroupID= 324965 in the next level MemberID =2 and GroupID = 248498.
But was is the PreviousPreviousMemberID or PreviousPreviousGroupID in the “The problematic solution” or the “Set-based Loop” ?
Second Point: instead of using the table “Groups “ in the “Set-based Loop”, I created a table Groups2. Both solutions should give the same resultset.But when I try this
select * from groups2 g2 inner join groups g1 on g1.clientid =g2.clientId where g1.OriginalMemberId<>g2.OriginalMemberId
I got 116 000 records. So there are some problems in the data. I can fix two problems (sbl= Set-based Loop; ps= problematic solution)
1.Resultset for client 9015: 901552158695661559015521586912921812
select * from GroupsHistory where GroupId = 215869 and memberid=5 -- 2 solutions level 3
select * from GroupsHistory where GroupId = 132273 and memberid=5 -- level 2; sbl
select * from GroupsHistory where GroupId = 129218 and memberid=12 -- level 2; ps finished
select * from GroupsHistory where GroupId = 56615 and memberid=5 -- level 1; sbl finished
select * from GroupsHistory where GroupId = 98116 and memberid=12 -- level 1; here is no ps (why?)
Both solutions uses different branches in die History-Table. The problematic solution goes only one step down.
2.Resultset for client 530537: 530537831067447018553053783106742490621
select * from GroupsHistory where GroupId = 310674 and memberid=8 -- level 4
select * from GroupsHistory where GroupId = 249062 and memberid=1 -- level 3 ps finished
select * from GroupsHistory where GroupId = 148545 and memberid=5 -- 2 at level 2
select * from GroupsHistory where GroupId = 47018 and memberid=5 -- level 1 sbl finished
select * from GroupsHistory where GroupId = 75661 and memberid=5 -- level 1
The problematic solution goes only one step down. The set-based Loop finished in both examples at level 1.
In the problematic solution the code to set the variable to NULL is at a wrong position, so the code leave the While-Branch to early.
SELECT h.GroupId, h.MemberId
FROM dbo.GroupsHistory h
WHERE h.GroupId = @GroupId
AND h.MemberId = @MemberId)
SELECT @GroupId = PreviousGroupId,
@MemberId = PreviousMemberId
FROM GroupsHistory h
WHERE h.GroupId = @GroupId
AND h.MemberId = @MemberId;
IF @GroupId IS NULL
UPDATE g SET
OriginalGroupId = @GroupId,
OriginalMemberId = @MemberId
FROM Groups g
WHERE ClientId = @ClientId;
SET @GroupId = NULL; --wrong position
SET @MemberId = NULL; --wrong position