July 11, 2012 at 11:23 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:29 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.:-)
July 12, 2012 at 2:08 am
Your method works well but doesn't adapt to other branches of the organisation, so it's not particularly scalable.
Consider an organisation with structure:
Director -> Assistant Director -> Sales Manager -> Sales Employee -> Junior Sales Employee
-> Ops Manager -> Ops Employee
-> Ops Employee
-> IT Manager -> IT Employee
-> IT Employee
-> IT Apprentice
If I executed your procedure as an Ops employee, I would only see myself and the other Ops employee, not any other employee at my level or below in other departments.
If this is what you're aiming for - great. Will work well for tight hierarchical organisations. But most organisations are fluid now with overlapping job roles. You'll find your method won't be a perfect fit.
Have you considered storing your hierarchical data as a string set, in the form x.y.z? Or rewriting the CTE to count up and down the levels as it executes, returning data according to allowed level?
---
Note to developers:Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.
July 12, 2012 at 2:23 am
Yes, you may be correct.
This is just tailor made to fit my requirement. But, its good that you have brought it to my notice.
Can you post a sample data set like the one I posted or may be a link that would throw some more light on the requirement of generalizing the solution?
It would be great if you could do so...I'd love to have a look at that and learn how to Generalize it.
Thank you so much for the reply.
July 12, 2012 at 7:14 am
If you want to abstract hierarchies so they can cover many types of relationships, like these examples, you do it the way you do any many-to-many relationship in a relational database, with a "join table".
If you want something a little more structured than that, you can do multiple hierarchies in the same database. For example, Executive Assistants usually answer to a single person, but a whole department might answer to another whole department, so you'd need two hierarchies there, one in the "People" table (whatever name you give that table), one in the "Departments" table. You might also need one to map that any "Marketing Assistant" can answer to any "Salesperson", which would be by job title instead of individual or department, and again, that's just a matter of adding mapping columns to the "Positions" table.
Keep in mind that organization structures change infrequently (in most cases), and thus this might be a better candidate for a nested-sets hierarchy than for an adjacency hierarchy. Much faster queries. (Google/Bing/whatever, "nested sets hierarchy" to get details on how to do those. They're MUCH faster to query than "ID", "SeniorID" hierarchies, which are called "adjacency" hierarchies. Slower to update, but faster to query.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 12, 2012 at 9:40 pm
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply