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 ShawFor 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