Tsql Recursive query - last active Id

Question

I have two tables with data like this (T-sql code to create):

CREATE TABLE [dbo].[IdChanges](
    [ReplacedId] [int] NOT NULL,
    [OverrideId] [int] NOT NULL 
);
INSERT INTO dbo.IdChanges(ReplacedId,OverrideId)VALUES(17,18);
INSERT INTO dbo.IdChanges(ReplacedId,OverrideId)VALUES(18,19);
INSERT INTO dbo.IdChanges(ReplacedId,OverrideId)VALUES(19,20);
INSERT INTO dbo.IdChanges(ReplacedId,OverrideId)VALUES(12,13);
INSERT INTO dbo.IdChanges(ReplacedId,OverrideId)VALUES(13,14);

CREATE TABLE [dbo].[IdActivity](
    [Id] [int] NOT NULL,
    [IsActive] [bit] NOT NULL   
);
INSERT INTO dbo.IdActivity(Id,IsActive)VALUES(14,1);
INSERT INTO dbo.IdActivity(Id,IsActive)VALUES(20,1);
INSERT INTO dbo.IdActivity(Id,IsActive)VALUES(17,0);
INSERT INTO dbo.IdActivity(Id,IsActive)VALUES(18,0);
INSERT INTO dbo.IdActivity(Id,IsActive)VALUES(19,0);
INSERT INTO dbo.IdActivity(Id,IsActive)VALUES(12,0);
INSERT INTO dbo.IdActivity(Id,IsActive)VALUES(13,0);
go

How can i get (by recursive cte query or other way) result with each ReplacedId and Last Override id in the chain of changes. I expects the result exactly like below. 5 rows ...Null in result are possible

ReplacedId   LastOverrideId
17           20
18           20
19           20
12           14
13           14

Answer 1

Recursive CTE is the way to go here. Make sure your IdChanges don't have loops before executing.

;WITH RecursiveActivities AS
(
    SELECT
        OriginalActivityID = I.Id,
        OverrideActivityID = I.Id,
        Level = 0
    FROM
        [dbo].[IdActivity] AS I

    UNION ALL

    SELECT
        OriginalActivityID = R.OriginalActivityID,
        OverrideActivityID = I.OverrideId,
        Level = R.Level + 1
    FROM
        RecursiveActivities AS R
        INNER JOIN [dbo].[IdChanges] AS I ON R.OverrideActivityID = I.ReplacedId
),
MaxLevelByActivity AS
(
    SELECT
        R.OriginalActivityID,
        MaxLevel = MAX(R.Level)
    FROM
        RecursiveActivities AS R
    GROUP BY
        R.OriginalActivityID
)
SELECT
    R.OriginalActivityID,
    R.OverrideActivityID
FROM
    RecursiveActivities AS R
    INNER JOIN MaxLevelByActivity AS M ON
        R.OriginalActivityID = M.OriginalActivityID AND
        R.Level = M.MaxLevel

If you don't want to see activities that don't have changes, just add WHERE R.OriginalActivityID <> R.OverrideActivityID at the end.