July 3, 2013 at 7:36 am
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?
July 3, 2013 at 7:51 am
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
July 3, 2013 at 7:53 am
also, i think you had your joins backwards...
this seems to show me everything correct:
/*SuperVisorManager1Manager2
ramNULLNULL
surehramNULL
vimalsurehram
*/
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
July 3, 2013 at 9:07 am
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
July 4, 2013 at 5:54 am
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply