Click here to monitor SSC
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 Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 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
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1134 Visits: 3229
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
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14942 Visits: 38939
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
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1134 Visits: 3229
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 Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 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