August 22, 2011 at 12:36 am
Hi,
I have table
EmpID EmpName MgrID
1 Indraneel NULL
2 Natraj 1
3 Sarat 2
4 Bhargav 3
5 Vijay NULL
6 Ajay 5
7 Suresh 6
NULL in MgrID mean he is the CEO.
My question is , if i have an employee no and i want to track his manger and his manger till the CEO(full mangers chain of the employee). How can we do this ? This was asked to me in an interview.
I said that, we can do this by using looping and temp variable. We will the run loop till the MgrID is Null.
But the interviewer is not satisfied with my answer. He asked me an alternate way other than loops or Cursors. Can some one throw some light on this issue.
August 22, 2011 at 1:41 am
Your answer to the interviewer is not wrong, you can use any looping mechanism ( cursor or while loop ) for this. But there are 2 more mechanisms of deriving parent child relation ( in ur case Employee-Manager) .
To explain the 2 methods, let me put out some sample data
Use tempdb
GO
declare @tab Table
(
ID integer identity (1,1)
,EmployeeName varchar(30)
,ManagerID integer
)
insert into @tab (EmployeeName, ManagerID )
select 'President & CEO' , null
union all select 'VP - Sales' , 1
union all select 'VP - R&D' , 1
union all select 'VP - HRD' , 1
union all select 'GM - Sales - US' , 2
union all select 'GM - Sales - UK' , 2
union all select 'GM - Sales - Ind' , 2
union all select 'GM - Sales - Aus' , 2
union all select 'GM - Sales - Africa' , 2
union all select 'GM - R&D - US' , 3
union all select 'GM - R&D - UK' , 3
union all select 'GM - R&D - Ind' , 3
union all select 'GM - R&D - Aus' , 3
union all select 'GM - R&D - Africa' , 3
union all select 'GM - HRD - US' , 4
union all select 'GM - HRD - UK' , 4
union all select 'GM - HRD - Ind' , 4
union all select 'GM - HRD - Aus' , 4
union all select 'GM - HRD - Africa' , 4
select * from @tab
1. Recursive CTE ( starting SQL 2005 and above)
--== Recursive CTE
; With AnchorCTE AS
(
--== Anchor column; Asssume this to understand how CTE works
-- 1. An internal table will be built by SQL with the anchor row
select ID , EmployeeName , ManagerID , cast( NULL as varchar(30)) Manager
from @tab
where ManagerID is null --== ManagerID null is the CEO
-- 2. Rows coming out of recursion will be appended to the
-- internal table
union all
-- 3. Here is whre recursion starts; Each row in the internal table
-- will be JOINED to the main @tab table. Matching rows will be
-- appended to the internal table
select MainTable.ID , MainTable.EmployeeName , MainTable.ManagerID , Rec.EmployeeName Manager
from @tab MainTable
inner join AnchorCTE Rec
on MainTable.ManagerID = Rec.ID
)
Select * from AnchorCTE
option ( maxrecursion 0) -- Important part of the recursive CTE.
Read this online MSDN article on CTEs and how to leverage CTEs for recursion Recursive Queries Using Common Table Expressions
2. Inline Sub-queries
--== Subquery Technique
select OuterTable.ID , OuterTable.EmployeeName , OuterTable.ManagerID ,
Manager = ( Select InnerTable.EmployeeName
from @tab InnerTable
where InnerTable.ID = OuterTable.ManagerID )
from @tab OuterTable
I haven't extensive testing on which method performs the fastest. You test it for yourself and find out 🙂
Hope this helps!
August 22, 2011 at 1:48 am
Hey thx for your instant response. I have to check this solutions on my DB now.
August 22, 2011 at 1:51 am
indraneelandhavarapu-866099 (8/22/2011)
Hey thx for your instant response. I have to check this solutions on my DB now.
You're welcome. Drop by here if you don't understand the code and the explanation. Happy Learning 😎
August 24, 2011 at 6:47 am
Another option is to use the HierarchyID data type introduced in 2008. Following is a set of scripts which might help you. I had adapted that from an internet resource for a tech session, can't recall which one.
Edit: Googled and found the source: http://avishkarm.blogspot.com/2010/06/sql-server-2008-hierachy-id.html
--**************************************
--Created Date: 5 May 2011
--Description: example for HIERARCHYID data type
--Part of session on New Features of SQL Server 2008
--**************************************
--Comment: Select the 'Include Actual Execution plan' option before executing these queries (Ctrl + M)
--truncate table Employee_2008
if(object_id('Employee_2008') is null)
CREATE TABLE Employee_2008
(
EmpID INT PRIMARY KEY NOT NULL IDENTITY,
EmpName VARCHAR(255) NOT NULL,
Title VARCHAR(255) NOT NULL,
HierarchicalLevel HIERARCHYID --Note the new data type here
)
GO
--Adding root node
--HIERARCHYID::GetRoot() is static method which returns the root node of a hierarchy
INSERT INTO Employee_2008 (EmpName, Title, HierarchicalLevel)
VALUES ('Akram', 'Chief Technology Officer', HIERARCHYID::GetRoot())
GO
SELECT EmpID, EmpName, Title, HierarchicalLevel, HierarchicalLevel.ToString() AS [Position]
FROM Employee_2008
GO
--------------------------------------------------------------------------------------------
--Adding child nodes
--Insert the first child node of the root node
--Get the root node we wish to insert a descendant of
DECLARE @CTONode HIERARCHYID
SELECT @CTONode = HIERARCHYID::GetRoot() FROM Employee_2008
INSERT INTO Employee_2008 (EmpName, Title, HierarchicalLevel)
VALUES ('Ranjit', 'DEV Manager', @CTONode.GetDescendant(NULL, NULL))
GO
--Now let's insert the second child node of the root node
--Get the root node we wish to insert a descendant of
DECLARE @CTONode HIERARCHYID
SELECT @CTONode = HIERARCHYID::GetRoot() FROM Employee_2008
-- Determine the last child position
DECLARE @LastChildPosition HIERARCHYID
SELECT @LastChildPosition = MAX(HierarchicalLevel) FROM Employee_2008
WHERE HierarchicalLevel.GetAncestor(1) = @CTONode
INSERT INTO Employee_2008 (EmpName, Title, HierarchicalLevel)
VALUES ('Adil', 'TEST Manager', @CTONode.GetDescendant(@LastChildPosition, NULL))
GO
SELECT EmpID, EmpName, Title, HierarchicalLevel, HierarchicalLevel.ToString() AS [Position] FROM Employee_2008
GO
--------------------------------------------------------------------------------------------
--Create SP to do child insertion
if exists(select * from sysobjects where name = 'AddEmployee_2008' and type = N'P')
drop procedure AddEmployee_2008
go
CREATE PROCEDURE AddEmployee_2008
(@ReportsToID INT, @EmpName VARCHAR(255), @Title VARCHAR(255))
AS
BEGIN
-- Get the root node we wish to insert a descendant of
DECLARE @ReportsToNode HIERARCHYID
SELECT @ReportsToNode = HierarchicalLevel FROM Employee_2008
WHERE EmpID = @ReportsToID
-- Determine the last child position
DECLARE @LastChildPosition HIERARCHYID
SELECT @LastChildPosition = MAX(HierarchicalLevel) FROM Employee_2008
WHERE HierarchicalLevel.GetAncestor(1) = @ReportsToNode
INSERT INTO Employee_2008 (EmpName, Title, HierarchicalLevel)
VALUES (@EmpName, @Title, @ReportsToNode.GetDescendant(@LastChildPosition, NULL))
END
GO
--add remaining nodes of tree using created stored procedure
EXECUTE AddEmployee_2008 2, 'Chandan', 'DEV Leader'
EXECUTE AddEmployee_2008 2, 'Sudeep', 'DEV Leader'
EXECUTE AddEmployee_2008 3, 'Ashraf', 'DEV Leader'
EXECUTE AddEmployee_2008 4, 'Dheeraj', 'DEV Engineer'
EXECUTE AddEmployee_2008 4, 'Hem', 'DEV Engineer'
EXECUTE AddEmployee_2008 4, 'Gaurav', 'DEV Engineer'
EXECUTE AddEmployee_2008 5, 'Uday', 'DEV Engineer'
EXECUTE AddEmployee_2008 5, 'Shayam', 'DEV Engineer'
EXECUTE AddEmployee_2008 6, 'Mukesh', 'TEST Engineer'
EXECUTE AddEmployee_2008 6, 'Sarfaraz', 'TEST Engineer'
select * from Employee_2008
--------------------------------------------------------------------------------------------
https://sqlroadie.com/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply