October 10, 2016 at 11:01 am
Luis, one question to your solution: Isn't it "unsafe" to rely on @@ROWCOUNT?
I experimented with this sort of code in the summer, but settled for a different approach due to strange things happening to my data when testing on @@ROWCOUNT... :crazy:
Thus, I will definitely give it a try once more, after I have digested your ideas and comments.
My current approach works - but is really ugly (a predefined number of iterations proved much faster than a solution which itself decided on a "sufficient" number of iterations through levels). I realized that it was the same amount of work (for the algorithm) to decide if it was finished as just doing the work. So in effect, my "elegant" solution used more than double the labor as needed - always!...:(
October 10, 2016 at 12:24 pm
hjp (10/10/2016)
Luis, one question to your solution: Isn't it "unsafe" to rely on @@ROWCOUNT?I experimented with this sort of code in the summer, but settled for a different approach due to strange things happening to my data when testing on @@ROWCOUNT... :crazy:
Thus, I will definitely give it a try once more, after I have digested your ideas and comments.
My current approach works - but is really ugly (a predefined number of iterations proved much faster than a solution which itself decided on a "sufficient" number of iterations through levels). I realized that it was the same amount of work (for the algorithm) to decide if it was finished as just doing the work. So in effect, my "elegant" solution used more than double the labor as needed - always!...:(
I wouldn't call it unsafe, but it might be if you don't know how it works. In a simple code like this, I've never seen it go nuts.
Do you have an example on what went wrong? No need to post real names, data or code, just enough to replicate the problem.
October 10, 2016 at 9:59 pm
No, I understand the problem and I have seen this solution before.
Uncommenting the loop returned the same results because the loop is supposed to run only once.
October 11, 2016 at 6:28 am
John Hick-456673 (10/10/2016)
No, I understand the problem and I have seen this solution before.Uncommenting the loop returned the same results because the loop is supposed to run only once.
Here's an example using one member with 6 groups and one member with a single group. If you uncomment the WHILE statement, the result will vary with the member that has history.
IF OBJECT_ID( 'dbo.GroupsHistory') IS NOT NULL --Prevent errors on reruns
DROP TABLE dbo.GroupsHistory;
CREATE TABLE dbo.GroupsHistory( --Create the table with relevant columns
MemberId int NOT NULL,
GroupId int NOT NULL,
PreviousMemberId int NULL,
PreviousGroupId int NULL,
StartDate date NOT NULL,
Level int NOT NULL --This column wasn't available on the original problem. It's here to help with test data generation.
);
IF OBJECT_ID( 'dbo.Groups') IS NOT NULL --Prevent errors on reruns
DROP TABLE dbo.Groups;
CREATE TABLE dbo.Groups( --Create the table with relevant columns
ClientId int IDENTITY NOT NULL,
MemberId int NOT NULL,
GroupId int NOT NULL,
OriginalMemberId int NULL,
OriginalGroupId int NULL
);
INSERT INTO dbo.GroupsHistory(MemberId, GroupId, PreviousMemberId, PreviousGroupId, StartDate, Level)
VALUES (1,1,NULL,NULL,'20160101', 1)
,(2,1,NULL,NULL,'20160101', 1)
,(1,2,1,1,'20160201',2)
,(1,3,1,2,'20160301',3)
,(1,4,1,3,'20160401',4)
,(1,5,1,4,'20160501',5)
,(1,6,1,5,'20160601',6)
INSERT INTO Groups(MemberId,GroupId)
VALUES(1,6),(2,1)
UPDATE g SET
OriginalGroupId = ISNULL( h.PreviousGroupId, h.GroupId), --Uses ISNULL for first time clients
OriginalMemberId = ISNULL( h.PreviousMemberId, h.MemberId)
FROM dbo.Groups g
JOIN dbo.GroupsHistory h ON g.GroupId = h.GroupId AND g.MemberId = h.MemberId;
WHILE @@ROWCOUNT > 0 --Update until there's no previous group for any Client
UPDATE g SET
OriginalGroupId = h.PreviousGroupId,
OriginalMemberId = h.PreviousMemberId
FROM dbo.Groups g
JOIN dbo.GroupsHistory h ON g.OriginalGroupId = h.GroupId AND g.OriginalMemberId = h.MemberId
WHERE h.PreviousGroupId IS NOT NULL;
SELECT * FROM Groups;
GO
DROP TABLE Groups,GroupsHistory;
October 11, 2016 at 9:50 am
OK, I see it now, thanks!
Viewing 5 posts - 46 through 49 (of 49 total)
You must be logged in to reply to this topic. Login to reply