Super manager
- Hello Every body,
I have an requirement where i have an employee table with their
managers reported.
Let me give you structure of the employee table
Empno Empname Mgr
1 Srinivas 1
2 sampath 1
3 raju 2
4 sekhar 4
5 lakhi 4
6 vishnu 5
Now my requirenment is to show for every employee his super manager
where in the above case srinivas(1) and sekhar(4) are super manager.
it should look like
Empno Empname supermgrname supermgrid
2 Sampath srinivas 1
3 raju srinivas 1
5 lakhi sekhar 4
6 vishnu sekhar 4
Like this.can any body help me out in building this query
Thank you
Raj Deep.A
-
DECLARE @upd int
CREATE TABLE #temp (Empno int, Empname varchar(20), Empnamesupermgrname varchar(20), supermgrid int)
INSERT INTO #temp (Empno, Empname, Empnamesupermgrname, supermgrid)
SELECT e.Empno, e.Empname, m.Empname AS [Empnamesupermgrname], m.Empno AS [supermgrid]
FROM [employee] e
INNER JOIN [employee] m
ON m.Empno = e.Mgr
AND m.Empno != e.Empno
SET @upd = @@ROWCOUNT
WHILE @upd > 0
BEGIN
UPDATE t
SET t.supermgrid = m.Mgr
FROM #temp t
INNER JOIN [employee] m
ON m.Empno = t.supermgrid
AND m.Empno != m.Mgr
SET @upd = @@ROWCOUNT
END
SELECT Empno, Empname, Empnamesupermgrname, supermgrid FROM #temp
DROP TABLE #temp
Far away is close at hand in the images of elsewhere.
Anon. -
Hello,
I'm not very skilled in use of hierarchy, so most probably there are better ways to do it, but here is one possible solution. It uses UDF - and I think you can't avoid the use of UDF in hierarchy, unless you have a fixed (and low) number of hierarchy levels.
/*test data*/
create table employee (empno int, empname varchar(20), mgr int)
insert into employee (empno, empname, mgr) values (1,'Srinivas', 1)
insert into employee (empno, empname, mgr) values (2,'Sampath', 1)
insert into employee (empno, empname, mgr) values (3,'raju', 2)
insert into employee (empno, empname, mgr) values (4,'sekhar', 4)
insert into employee (empno, empname, mgr) values (5,'lakhi', 4)
insert into employee (empno, empname, mgr) values (6,'vishnu', 5)
/*function*/
ALTER FUNCTION dbo.get_top_manager (@empid int)
RETURNS INT
AS
BEGIN
DECLARE @manid int
SET @manid = (SELECT mgr FROM employee WHERE empno = @empid)
WHILE @manid <> @empid
BEGIN
SET @empid = @manid
SET @manid = (SELECT mgr FROM employee WHERE empno = @empid)
END
RETURN @manid
END
/*use of the function in SELECT*/
SELECT empno, empname, dbo.get_top_manager(empno)
FROM employee
This way you get the manager ID, and it shouldn't be hard to add a name to it. Again, I warn you that this gives required result, but I'm not sure about performance. I think other people will come up with better solution.
EDIT: ... and as I see, David posted his solution even before I completed writing my post
-
and as I see, David posted his solution even before I completed writing my post WOW is this a first then, lol
It's normally the other way round
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply