473,713 Members | 6,107 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Manager Hierarchy (Recursive)

2 New Member
I am trying to write a stored procedure that could create a table called tblManagerHiera rchy. It is a table that contains recursive data.
The data is coming from tblEmployee where it contains that EmpNum and ManagerEmpNum.

tblEmployee (EmpNum, ManagerEmpNum)

The tblManagerHiera rchy should contain Manager's EmpNum plus direct and indirect subordinates' EmpNum.
Basically it should contain everybody who reports to a Manager directly and indirectly. So CEO should contain everyone in the company, and general manager only has few..

The table's structure should be like this.

tblManagerHiera rchy (ManagerEmpNum, EmpNum)

I need to use store procedure that contains cursor and loop.
Any sample of code is appreciated.

Thanks.
Jul 11 '07 #1
3 5003
Infide
28 New Member
I am trying to write a stored procedure that could create a table called tblManagerHiera rchy. It is a table that contains recursive data.
The data is coming from tblEmployee where it contains that EmpNum and ManagerEmpNum.

tblEmployee (EmpNum, ManagerEmpNum)

The tblManagerHiera rchy should contain Manager's EmpNum plus direct and indirect subordinates' EmpNum.
Basically it should contain everybody who reports to a Manager directly and indirectly. So CEO should contain everyone in the company, and general manager only has few..

The table's structure should be like this.

tblManagerHiera rchy (ManagerEmpNum, EmpNum)

I need to use store procedure that contains cursor and loop.
Any sample of code is appreciated.

Thanks.
That's quite the task. But if you want a recursive function, here you go..

Expand|Select|Wrap|Line Numbers
  1. CREATE FUNCTION dbo.GetHierarchy(@ManagerID int)
  2. RETURNS @Hierarchy table (ParentID int,
  3.                    ChildID int)
  4. AS
  5. BEGIN
  6.  
  7.     --EMPNUM, --MANAGEREMPNUM, --TblEmployee
  8.  
  9.     declare @ChildID int
  10.     if (@ManagerID int is null)
  11.     begin
  12.         declare curEmp cursor local fast_forward for 
  13.         select empNum
  14.         from tblEmployee
  15.  
  16.         open curEmp
  17.  
  18.         fetchnext from curEmp into @ChildID
  19.         while @@fetch_status = 0
  20.         begin
  21.             insert into @Hierarchy(ParentID,ChildID)
  22.             values(@ManagerID,@ChildID)
  23.  
  24.             insert into @Hierarchy(ParentID,ChildID)
  25.             select * from dbo.GetHierarchy(@ChildID)
  26.  
  27.         fetchnext from curEmp into @ChildID
  28.         end
  29.  
  30.         close curEmp
  31.         deallocate curEmp
  32.     end
  33.     else
  34.     begin
  35.         declare curEmp cursor local faast_forward for 
  36.         select empNum
  37.         from tblEmployee
  38.         where managerempnum = @ManagerID
  39.  
  40.         open curEmp
  41.  
  42.         fetchnext from curemp into @ChildID
  43.         while @@fetch_status = 0
  44.         begin
  45.             insert into @Hierarchy(ParentID,ChildID)
  46.             values (@ManagerID,@ChildID)
  47.  
  48.             insert into @Hierarchy(parentID,childID)
  49.             select * from dbo.GetHierarchy(@ChildID)
  50.  
  51.         fetchnext from curemp into @ChildID
  52.  
  53.         end
  54.  
  55.  
  56.  
  57.     end
  58.     return
  59.  
  60. end
  61.  
Jul 12 '07 #2
Infide
28 New Member
Or better yet, if you're using sql server 2005 I just found this in the documentation.
Expand|Select|Wrap|Line Numbers
  1. USE AdventureWorks;
  2. GO
  3. WITH DirectReports(ManagerID, EmployeeID, EmployeeLevel) AS 
  4. (
  5.     SELECT ManagerID, EmployeeID, 0 AS EmployeeLevel
  6.     FROM HumanResources.Employee
  7.     WHERE ManagerID IS NULL
  8.     UNION ALL
  9.     SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1
  10.     FROM HumanResources.Employee e
  11.         INNER JOIN DirectReports d
  12.         ON e.ManagerID = d.EmployeeID 
  13. )
  14. SELECT ManagerID, EmployeeID, EmployeeLevel 
  15. FROM DirectReports ;
  16. GO
  17.  
  18.  
  19.  
Very nice
Jul 12 '07 #3
jzdoh
2 New Member
Thank you so much.
I tried your coding, but I got different twist of results than what I expected.
Instead having all the direct and indirect manager's emp num, it listed everyone who is under a manager's chain of command in the relationshiop of Direct Report.

Right Now, it is retreving like this:
MgrEmpNum : EmpNum
CEO's : VP's
VP's : AVPs
AVPs : GM's
GMs : TeamLeader

What I am trying to get is like this:

MgrEmpNum : EmpNum
CEO's : VP's
CEO's : AVPs
CEO's : GM's
CEO's : TeamLeader
.......
VP's : AVPs
VP's : GM's
VP's : TeamLeader

CREATE FUNCTION dbo.fnPMMgrHier archy(@ManagerI D char(11))
RETURNS @Hierarchy table (ParentID char(11), ChildID char(11))
AS
BEGIN

--EMPNUM, --MANAGEREMPNUM, --TblEmployee

declare @ChildID char(11)

If (@ManagerID is null)
begin
declare @curEmp CURSOR
set @curEmp = CURSOR LOCAL FAST_FORWARD FOR
select sEmpNum
from tblPMEmployee

open @curEmp

fetch next from @curEmp into @ChildID
while @@fetch_status = 0
begin
insert into @Hierarchy(Pare ntID,ChildID)
values(@Manager ID,@ChildID)

insert into @Hierarchy(Pare ntID,ChildID)
select * from dbo.fnPMMgrHier archy(@ChildID)

fetch next from @curEmp into @ChildID
end

close @curEmp
deallocate @curEmp
end
else
begin
set @curEmp = CURSOR LOCAL FAST_FORWARD FOR
select sEmpNum
from tblPMEmployee
where sTmShtSupvEmpNu m = @ManagerID

open @curEmp

fetch next from @curEmp into @ChildID
while @@fetch_status = 0
begin
insert into @Hierarchy(Pare ntID,ChildID)
values (@ManagerID,@Ch ildID)

insert into @Hierarchy(pare ntID,childID)
select * from dbo.fnPMMgrHier archy(@ChildID)

fetch next from @curEmp into @ChildID

end

end
return

end
Attached Files
File Type: txt submit.txt (1.4 KB, 404 views)
Jul 12 '07 #4

Sign in to post your reply or Sign up for a free account.

Similar topics

0
1258
by: Tero Saarni | last post by:
Hi, I have a module with several test case classes which each have several test methods: class Foo(unittest.TestCase): def testMethod1(self): def testMethod2(self): class Bar(unittest.TestCase):
10
2101
by: KemperR | last post by:
Dear All, may be some of you can help me with an XSLT example how to solve the following challange. For the XML below I want to find out the maximum hierarchy level for a specific element in my XSLT. The result for the example (searching for <A/>) should be 4 as the element A is nested 4 times maximum. I guess I have to use somehow the count function with 'following::A' axes. But I could not get that to work yet.
3
5208
by: Fresh Air Rider | last post by:
Hi SQL Gurus Could anyone please explain how the following stored procedure can be amended to retrieve Subordinates in alphabetical order ? The example below simply retrives records in the order in which they were entered. It sounds very easy but I can't sort it out ?
7
7435
by: Kevin Newman | last post by:
I've been toying with a namespace manager, and wanted to get some input. So what do you think? if (typeof com == 'undefined') var com = {}; if (!com.unFocus) com.unFocus = {}; com.unFocus.Namespaces = new function() { this.register = function(namespace) { namespace = namespace.split('.');
7
11018
by: Bruce Hensley | last post by:
I don't know how to begin on a query (SELECT statement) to find all the tasks assigned to an arbitrary manager (say, staffID='JSmith') and her organization, that is, assigned to all her underlings, and their underlings, and .... For that matter, I don't even know how to find everyone in her organization (at all levels). - All individuals have only one manager - Tasks are assigned to individuals - A manager at any level may have direct...
2
2796
by: FrankEBailey | last post by:
I've been reading up on Modified Preorder Tree Traversal and it's definitely ideal for the kind of tree structures I need to model for my company's multi-level sales hierarchy. I've implemented the database side already in SQL Server 2000 and can retrieve all child nodes based on the left and right IDs of the current node. My problem is displaying the tree so that correct indentation can be used to show the relationships between the...
4
3588
by: the6campbells | last post by:
for the conext of this example imagine a simple part table where a part can only have a single parent. try to express a cte where the result set must be balanced. That is, the result set repeats node B and E sufficient times that the tree is as deep as node G. insert into Part ( PartId, PartName, ParentPart) values ( 1,'A',null), (2,'B',1), (3,'C',1), (4,'D',1), (5,'E',3), (6,'F',4), (7,'G',6) The following query will not parse as...
2
1334
by: Tem | last post by:
I have a table that looks like this. Each row is a reply or a topic when IsAReplyTo = 0 I need to write a query that pulls all rows of a hierarchy of a given ID Table Forum ID IsAReplyTo 4 0 9 4 27 4 63 27
11
1398
by: Simon Woods | last post by:
Hi I have this recursive function and I want to walk the inheritance hierarchy to set field values .... the generic T is constrainted as the base class of the inheritance hierarchy Friend Shared Function InjectFieldValues(ByVal p_def As T, ByVal p_properties As PropertyMaps) As T
0
8795
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8701
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9302
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
9067
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9007
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7941
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
4459
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
3154
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2509
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.