CREATE TABLE [dbo].[User](

[UserId] [int] NOT NULL,

[Active] [bit] NOT NULL

) ON [PRIMARY]

CREATE TABLE [dbo].[UserReporting](

[UserId] [int] NOT NULL,

[ReportingUserId] [int] NULL

) ON [PRIMARY]

INSERT INTO [dbo].[User] ([UserId] ,[Active]) VALUES (1,1)

INSERT INTO [dbo].[User] ([UserId] ,[Active]) VALUES (2,1)

INSERT INTO [dbo].[User] ([UserId] ,[Active]) VALUES (3,1)

INSERT INTO [dbo].[User] ([UserId] ,[Active]) VALUES (4,0)

INSERT INTO [dbo].[User] ([UserId] ,[Active]) VALUES (5,1)

INSERT INTO [dbo].[User] ([UserId] ,[Active]) VALUES (6,1)

INSERT INTO [dbo].[User] ([UserId] ,[Active]) VALUES (7,1)

INSERT INTO [dbo].[UserReporting] ([UserId] ,[ReportingUserId]) VALUES (2,1)

INSERT INTO [dbo].[UserReporting] ([UserId] ,[ReportingUserId]) VALUES (3,2)

INSERT INTO [dbo].[UserReporting] ([UserId] ,[ReportingUserId]) VALUES (4,3)

INSERT INTO [dbo].[UserReporting] ([UserId] ,[ReportingUserId]) VALUES (5,4)

INSERT INTO [dbo].[UserReporting] ([UserId] ,[ReportingUserId]) VALUES (6,4)

INSERT INTO [dbo].[UserReporting] ([UserId] ,[ReportingUserId]) VALUES (1,NULL)

User id and reporting user id both are userid from User table.

Required Output

User Id -- Reporting User Id

1 -- NULL

2 -- 1

3 -- 2

5 -- 3

6 -- 4

5's reporting user id is actually 4 as per ReportingUser table. But as 4 is Inactive in User table, the output should show 4's reporting id 3 as the reporting user id for 5. We need to go up to the top until we find an Active reporting user id.