April 23, 2014 at 12:46 am
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
April 23, 2014 at 1:49 am
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.
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
April 23, 2014 at 4:39 am
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.
April 23, 2014 at 5:31 am
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]
April 23, 2014 at 5:43 am
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