Getting heirarchy

  • I'm struggling to prepare a recursive t-sql for the below scenario

    User table

    user id (int)

    active (bit)

    Reporting table

    userid (int)

    reporting_userid (int)

    Assume I have users a, b, c; here a is supervisor of b and b is supervisor of c. I need to get the supervisor details as below

    user supervisor

    a <null>

    b a

    c b

    The twist here is, if any userid is not active, we should take that user's supervisor as the supervisor of the current user. Ex: in the above case, if b is in active, we should be able to get the below output.

    user supervisor

    a <null>

    c a

    We should be able to traverse up to get supervisor until we get an active supervisor user Please advise the techniques in t-sql to easily do this. Thanks

  • We'll need some sample data and a table of expected results. Read this article [/url]which describes how to create and post your scripts.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • 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.

  • well it's a llittle tricky

    DECLARE @User AS TABLE ([UserId] [int] NOT NULL, [Active] [bit] NOT NULL,USerName VARCHAR(10))

    DECLARE @UserReporting AS TABLE ([UserId] [int] NOT NULL, [ReportingUserId] [int] NULL)

    INSERT INTO @User

    VALUES (1,1,'user1'),(2,1,'user2'),(3,1,'user3'),(4,0,'user4'),(5,1,'user5'),(6,1,'user6'),(7,1,'user7')

    INSERT INTO @UserReporting ([UserId] ,[ReportingUserId])

    VALUES (1,NULL),(2,1), (3,2), (4,3), (5,4), (6,4)

    ;WITH cte AS(

    SELECT ur.UserId, ur.ReportingUserId, Null as Activeparent

    FROM @UserReporting ur --INNER JOIN @User u ON u.UserId = ur.UserId

    WHERE ur.ReportingUserId IS NULL

    UNION ALL

    SELECT ur.UserId, ur.ReportingUserId, CASE WHEN u.[Active] =1 THEN ur.ReportingUserId ELSE c.Activeparent END AS parent

    FROM @UserReporting ur INNER JOIN cte c ON c.userid = ur.ReportingUserId

    INNER JOIN @User u ON u.UserId = ur.ReportingUserId

    )

    SELECT c.userid, Activeparent FROM cte c

    INNER JOIN @User u ON u.UserId = c.userid

    WHERE u.[Active] =1

    Every rule in a world of bits and bytes, can be bend or eventually be broken
    MyBlog About Common dialog control
    A Visualizer for viewing SqlCommand object script [/url]

  • it's really wonderful... I can see that my CTE has incorrect joins!! Thanks a lot...

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply