SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Get Employee Managers Manger Query


Get Employee Managers Manger Query

Author
Message
RamG
RamG
SSC-Enthusiastic
SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)

Group: General Forum Members
Points: 117 Visits: 110
Hi

I want to take employee, Manager and the managers' manger from employee table. I've tried the below query its working if i have the three values (emp, manager1, manager2).

But f i have only two levels (emp and manger only) then its not working.


CREATE TABLE [#Employee](
[EmpID] [int] NOT NULL,
[EmpName] [varchar](50) NOT NULL,
[SupID] [int] NULL)
GO

INSERT INTO [#Employee] (EmpID, EmpName, SupID) VALUES (1, 'ram', 2)
INSERT INTO [#Employee] (EmpID, EmpName, SupID) VALUES (2, 'sureh', 3)
--INSERT INTO [#Employee] (EmpID, EmpName, SupID) VALUES (3, 'vimal', null)


select e1.empname employee,m1.empname Manager1, m2.empname Manager2 from
[#Employee] e1, [#Employee] m1, [#Employee] m2
where m1.EmpID = e1.SupID and m2.EmpID = m1.SupID




Can any one help me here?
Jason-299789
Jason-299789
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5071 Visits: 3232
You need to look at using a recursive CTE to traverse the Hierachy with a bit tweaking of the example in BoL (http://msdn.microsoft.com/en-us/library/ms186243(v=sql.105).aspx)

This should give you a good starting point


CREATE TABLE [#Employee](
[EmpID] [int] NOT NULL,
[EmpName] [varchar](50) NOT NULL,
[SupID] [int] NULL)
GO

INSERT INTO [#Employee] (EmpID, EmpName, SupID) VALUES (1, 'ram', 2)
INSERT INTO [#Employee] (EmpID, EmpName, SupID) VALUES (2, 'sureh', 3)
INSERT INTO [#Employee] (EmpID, EmpName, SupID) VALUES (3, 'vimal', null)

DECLARE @Emp varchar(50) = 'ram'


;WITH CTE_Traverse_hierarchy
AS
(
--Anchor
SELECT EmpId,EmpName,SupId,0 Lvl
FROM #Employee
Where EmpName=@Emp
UNION ALL
SELECT
E.EmpId,E.EmpName,E.SupId,Lvl+1 Lvl
FROM #Employee E
JOIN CTE_Traverse_hierarchy Parent on Parent.SupId=E.EmpId
)
Select *
from
CTE_Traverse_hierarchy



This will give you all the parents for a given Employee. After this its quite simple to move up the hierarchy.

_________________________________________________________________________
SSC Guide to Posting and Best Practices
Lowell
Lowell
SSC Guru
SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)

Group: General Forum Members
Points: 73378 Visits: 40968
also, i think you had your joins backwards...

this seems to show me everything correct:

/*SuperVisor Manager1 Manager2
ram NULL NULL
sureh ram NULL
vimal sureh ram
*/

CREATE TABLE [#Employee](
[EmpID] [int] NOT NULL,
[EmpName] [varchar](50) NOT NULL,
[SupID] [int] NULL)
GO

INSERT INTO [#Employee] (EmpID, EmpName, SupID) VALUES (1, 'ram', 2)
INSERT INTO [#Employee] (EmpID, EmpName, SupID) VALUES (2, 'sureh', 3)
INSERT INTO [#Employee] (EmpID, EmpName, SupID) VALUES (3, 'vimal', null)

SELECT
SuperVisors.empname SuperVisor,
Managers.empname Manager1,
SubManagers.empname Manager2

FROM [#Employee] SuperVisors
LEFT OUTER JOIN [#Employee] Managers
ON Managers.SupID = SuperVisors.EmpID --vimals id is someone elses supervisor id
LEFT OUTER JOIN [#Employee] SubManagers
ON Managers.EmpID = SubManagers.SupID



Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Jason-299789
Jason-299789
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5071 Visits: 3232
I miss read the requirement but seeing Lowells solution I saw the requirement, this works by making the results into a cross tab



CREATE TABLE [#Employee](
[EmpID] [int] NOT NULL,
[EmpName] [varchar](50) NOT NULL,
[SupID] [int] NULL)
GO

INSERT INTO [#Employee] (EmpID, EmpName, SupID) VALUES (1, 'ram', 2)
INSERT INTO [#Employee] (EmpID, EmpName, SupID) VALUES (2, 'sureh', 3)
INSERT INTO [#Employee] (EmpID, EmpName, SupID) VALUES (3, 'vimal', null)

DECLARE @Emp varchar(50) = 'ram'


;WITH CTE_Traverse_hierarchy
AS
(
--Anchor
SELECT EmpId RootEmpId ,EmpId,EmpName,SupId,0 Lvl
FROM #Employee
Where EmpName=@Emp
UNION ALL
SELECT
RootEmpId, E.EmpId,E.EmpName,E.SupId,Lvl+1 Lvl
FROM #Employee E
JOIN CTE_Traverse_hierarchy Parent on Parent.SupId=E.EmpId
Where
Lvl+1<=2
)
Select
MAX(CASE Lvl
WHEN 0 THEN EmpName
ELSE NULL
END) Supervisor
, MAX(CASE Lvl
WHEN 1 THEN EmpName
ELSE NULL
END) Manager
, MAX(CASE Lvl
WHEN 2 THEN EmpName
ELSE NULL
END) SubManager

from
CTE_Traverse_hierarchy




There are other improvements and it should work for any number of levels all you need to do is add Additional MAX clauses and Extent the range of the Where, possibly using a variable to define the number of Levels you want.

_________________________________________________________________________
SSC Guide to Posting and Best Practices
RamG
RamG
SSC-Enthusiastic
SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)

Group: General Forum Members
Points: 117 Visits: 110
Thanks for your help. Its working :-)

I've tried with left join to get the detail and got the results. Is this correct?



CREATE TABLE [#Employee](
[EmpID] [int] NOT NULL,
[EmpName] [varchar](50) NOT NULL,
[SupID] [int] NULL)
GO

INSERT INTO [#Employee] (EmpID, EmpName, SupID) VALUES (1, 'ram', 2)
INSERT INTO [#Employee] (EmpID, EmpName, SupID) VALUES (2, 'sureh', 3)
INSERT INTO [#Employee] (EmpID, EmpName, SupID) VALUES (3, 'vimal', null)

declare @Emp varchar(10)
select @Emp = 'ram'

;WITH CTE_emp_hierarchy AS
(
select
e1.empname employee,
m1.empname Manager1,
m2.empname Manager2
from
#employee e1
left join #employee m1 on (e1.supid = m1.empid )
left join #employee m2 on (m1.supid = m2.empid )
where
e1.empname = @Emp
)

select * from CTE_emp_hierarchy




Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search