query for employee hierarchy - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

query for employee hierarchy

Author

Topic

WoodHouse
Posting Yak Master

211 Posts

Posted - 2010-03-09 : 05:22:41

I have table data like this..


EMPID            DESIG           MANGRID
302003432	Sales Director	703053687
703017106	Sales Manager	302003432
703017280	Sales Manager	302003432
70305368	VC	        703053689
703041130	Sales Manager	302003432
703041140	Sales Manager	302003432
703041150	Sales Manager	302003432
703041160	Sales Manager	302003432
703041170	Sales Manager	302003432
703041180	Sales Manager	302003432
703041190	Sales Manager	302003432
703041200	Sales Manager	302003432
703041210	Sales Manager	302003432
703041220	Sales Manager	302003432
703041230	Sales Manager	302003432
703041240	Sales Manager	302003432
703042200	Sales Manager	302003432
703053687	RA	        70305368

I need query for employee hierarchy.

For example
1. If I pass sales manager Id it will display only his details
2. If I pass sales director ID it will display his details and will display under his sales manager details also.
3. If I pass RA director ID it will display his details along with his Sales Director & sales Manager details also
4. IF I pass VC ID it will display his details along with his RA, Sales Director & sales manager details also...

Help on this..

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-03-09 : 05:42:36

Here's logic I wrote for something similar. Here the table is called Employee and has usual columns (names etc) and a manager ID (who is also an employee). Edit as required.


WITH manager_tree AS (
SELECT
	e.[Id] AS [employeeID]
	, e.[Id] AS [stepID]
	, e.[managerId] AS [managerId]
	, 0 AS [level]
	, CAST(e.[firstName] + ' ' + e.[surname] AS VARCHAR(MAX)) AS [management Path]
	, CAST(e.[ID] AS VARCHAR(MAX)) AS [id Path]
FROM
	employee e

UNION ALL SELECT
	mt.[employeeId]
	, e2.[Id] AS [stepId]
	, e2.[managerID] AS [managerId]
	, mt.[level] + 1 AS [level]
	, CAST(mt.[management Path] + ' - ' + e2.[firstName] + ' ' + e2.[surname] AS VARCHAR(MAX))
	, CAST(mt.[id Path] + ',' + CAST(e2.[ID] AS VARCHAR(MAX)) AS VARCHAR(MAX)) AS [id Path]
FROM
	manager_tree mt
	JOIN employee e2 ON e2.[Id] = mt.[managerId]
WHERE
	e2.[Id] <> mt.[stepID]
	AND [id Path] NOT LIKE '%' + CAST(e2.[ID] AS VARCHAR(MAX)) + '%'
	)
SELECT
	*
FROM
	manager_tree

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-10 : 12:30:57

you've almost similar query given as example for recursive cte in books online

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources