Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Get Employee Managers Manger Query Expand / Collapse
Author
Message
Posted Wednesday, July 3, 2013 7:36 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 4, 2013 7:54 AM
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?
Post #1470053
Posted Wednesday, July 3, 2013 7:51 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, July 18, 2014 9:09 AM
Points: 870, Visits: 2,385
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
Post #1470062
Posted Wednesday, July 3, 2013 7:53 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:24 PM
Points: 12,876, Visits: 31,787
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1470063
Posted Wednesday, July 3, 2013 9:07 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, July 18, 2014 9:09 AM
Points: 870, Visits: 2,385
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
Post #1470103
Posted Thursday, July 4, 2013 5:54 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 4, 2013 7:54 AM
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



Post #1470414
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse