July 11, 2012 at 11:20 pm
Hi,
We have an ERP which is being used by a school. I have a requirement where there is a page in which an employee logs in and can see all the other employees who work under him, and I mean all the employees working under him, not only his Direct Subordinates. But, he should't see who is working above him ie: his Supervisors.
I have a table which has data for ~1000 employees of that school. I am posting sample DDL and Data which depicts my requirement as follows:
--Creating Table
Create Table Hierarchy_Test
(EmpId int,
SupervisorId int )
--Inserting Sample Data
Insert Into Hierarchy_Test
Select 1, Null
Union ALL
Select 2, 1
Union ALL
Select 3, 1
Union ALL
Select 4, 2
Union ALL
Select 5, 2
Union ALL
Select 6, 3
Union ALL
Select 7, 3
Union ALL
Select 8, 4
Union ALL
Select 9, 4
Union ALL
Select 10, 4
In this scenario, EmpId 1 is at the top of the Hierarchy and all people all the way till Emp Id 10 work under him. So, he should be able to see all the employees when he logs in. So, I need to Select all the employees that work under him(Directly/Indirectly).
Similarly, when EmpId2 logs in he should be able to view all the employees till EmpId 10 but not EmpId 1(his Supervisor).
I hope that explains my requirement. Any further questions about the requirement are still very welcome, I would do my best to explain further.
From what I think, this would be done using a Recursive CTE. I haven't done this before and I'm trying.
If anyone could provide a solution for the Sample Data then it would be very very helpful and would give me a push in the right direction to implement the same on the actual Data that I have.
Cheers to Everyone,
July 12, 2012 at 12:36 am
Well, no answers yet.
But, I've got good news. I solved it using Recursive CTE. This is the solution:
Declare @loginId int = 2
;With CTE
As
(
--Anchor Member Definition
Select EmpId From Hierarchy_Test Where SupervisorId = @loginId
Union ALL
--Recursive Member Definition
Select a.EmpId From Hierarchy_Test As a JOIN CTE As b ON a.SupervisorId = b.EmpId
)
Select * From CTE
Hope this helps others, like me, as well.:-)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply