×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

HR Recursive List using CTE but includes 'self' as level 0
2

thread183-1802435

HR Recursive List using CTE but includes 'self' as level 0

HR Recursive List using CTE but includes 'self' as level 0

(OP)
I am using a single table

CODE --> AssociateMasterTable

UniqueID   LastName     FirstName     ManagerID     Title
========   ========     =========     =========     =====
1          Smith        Frank                       VP_Sales
2          Moore        Bethany       1             Director_Sales
3          Williams     Teddy         1             Director_Marketing
4          Sanderlyn    Gill          2             Manager_Sales
5          Fredrick     Todd          3             Manager_Marketing
6          Bendricks    Tina          3             Manager_TradeShow
7          Potter       April         4             Supervisor
8          Forest       Rose          4             Supervisor
9          Black        Ben           5             Supervisor
10         Twitty       Jenifer       6             Supervisor
11         Smith        Lynn          7             SalesCoordinator
12         Wheeler      Tammy         8             SalesAdminAssistant
13         Getty        Jeffery       9             MarketingCoordinator
14         Silver       Hank          10            MarketingTradeShowAdminAssistant


CODE

WITH DirectReports(ManagerID, EmployeeID, Associate, Title, EmployeeLevel) AS   
(  
    SELECT ManagerID, UniqueID, LegalLastName + ', ' + LegalFirstName AS AssociateName, Title, 0 AS EmployeeLevel  
    FROM AssociateMasterTable 
    WHERE 
      managerID = 2  --Bethany

    UNION ALL  

    SELECT e.ManagerID, e.UniqueID, e.LegalLastName + ', ' + e.LegalFirstName, e.Title, EmployeeLevel + 1  
    FROM dbo.AssociateMasterTable AS e  
      INNER JOIN DirectReports AS d  
        ON e.ManagerID = d.EmployeeID   

)  
SELECT DR.ManagerID, MGR.LegalLastName + ', ' + MGR.LegalFirstName as Manager, DR.EmployeeID, DR.Associate, DR.Title, DR.EmployeeLevel   
FROM DirectReports DR
  INNER JOIN AssociateMasterTable as Mgr ON DR.ManagerID = Mgr.UniqueID
ORDER BY 
  DR.EmployeeLevel Asc, 
  MGR.LegalLastName + ', ' + MGR.LegalFirstName ASC,
  DR.Associate Asc

option (maxrecursion 0);

My purpose is to populate a drop down so that manager can see all associates names of everyone under their leg of management. ie manager of managers all the way down to the bottom level.

So with the sample code I seed with Bethany, I want the list to (not necessarily in order other than levels including Bethany, Gill, April, Rose, Lynn, Tammy

(side comment I was looking to see if I could do this in VBA first, this is that thread: VBA thread

RE: HR Recursive List using CTE but includes 'self' as level 0

2

If you want to include the employee that you have seeded you can just Union an additional query to pull the data directly from the associate table. Also make first level 1 so that your added employee will show a the top (added as level 0)


CODE

declare @LoggedOnEmployeeId int = 2

;
WITH DirectReports(ManagerID, EmployeeID, Associate, Title, EmployeeLevel) AS   
(  
    SELECT ManagerID, UniqueID, LastName + ', ' + FirstName AS AssociateName, Title, 1 AS EmployeeLevel  
    FROM dbo.AssociateMasterTable 
    WHERE 
      managerID = @LoggedOnEmployeeId --Bethany
   
    UNION ALL

    SELECT e.ManagerID, e.UniqueID, e.LastName + ', ' + e.FirstName, e.Title, EmployeeLevel + 1  
    FROM dbo.AssociateMasterTable  AS e 
      INNER JOIN DirectReports AS d  
        ON e.ManagerID = d.EmployeeID
)  
SELECT DR.ManagerID, MGR.LastName + ', ' + MGR.FirstName as Manager, DR.EmployeeID, DR.Associate, DR.Title, DR.EmployeeLevel   
FROM DirectReports DR
  INNER JOIN dbo.AssociateMasterTable  as Mgr ON DR.ManagerID = Mgr.UniqueID
UNION 
SELECT MGR.ManagerID, MGR.LastName + ', ' + MGR.FirstName as Manager, MGR.UniqueId, MGR.LastName + ', ' + MGR.FirstName as Associate, MGR.Title, 0
FROM  dbo.AssociateMasterTable  as Mgr
WHERE Mgr.UniqueId = @LoggedOnEmployeeId

  ORDER BY 
  DR.EmployeeLevel Asc, 
  MGR.LastName + ', ' + MGR.FirstName ASC,
  DR.Associate Asc

option (maxrecursion 0);


Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

RE: HR Recursive List using CTE but includes 'self' as level 0

(OP)
@Mark, Brilliant! I was trying to make the union in the front (top of the sql code) but placing at the bottom makes complete sense now. Also I tend to use variables and immediately smiled when I noticed you went there.

THANK YOU!!
Rob

RE: HR Recursive List using CTE but includes 'self' as level 0

(OP)
Follow up question for this script. If all users who reach this code, they get all associates, but if user with ID 'xxxxxxxxx' goes through this code they are restricted by not seeing Managing Directors (MDs).

Code I have so far:

CODE --> TSQL

DECLARE @AssociateID = 'xxxxxxxxx'

;WITH #DirectReports(ManagerID, EmployeeID, Associate, Title, EmployeeLevel) 
	AS (  
	SELECT ManagerID, AssociateID, PreferredLastName + ', ' + PreferredFirstName, Title, 1 AS EmployeeLevel  
	FROM AssociateMaster 
	WHERE 
	ManagerID = 'aaaaaaaaa'
	AND ActiveYn = 1
	AND AssociateID <> 'bbbbbbbbb'		

	UNION ALL  

	SELECT AM.ManagerID, AM.AssociateID, AM.PreferredLastName + ', ' + AM.PreferredFirstName, AM.Title, EmployeeLevel + 1  
	FROM AssociateMaster AS AM  
	INNER JOIN #DirectReports AS d  
	ON AM.ManagerID = d.EmployeeID  
	WHERE 
		AM.ActiveYn = 1
	AND d.Associate <> 'bbbbbbbb'		
		)  

	SELECT 
		DR.ManagerID, MGR.PreferredLastName + ', ' + MGR.PreferredFirstName AS ManagerPrefName, DR.EmployeeID as AssociateID, DR.Associate as AssociatePrefName, DR.Title, DR.EmployeeLevel   
	FROM #DirectReports DR
	INNER JOIN AssociateMaster as MGR ON DR.ManagerID = MGR.AssociateID
	WHERE
		AssociateID = 'aaaaaaaaa'
		AND ((@AssociateID = 'xxxxxxxxx' and DR.Title <> 'Managing Director')	--filter not showing MD's 
			      OR  (@AssociateID <> 'xxxxxxxxx')) 


	UNION

	SELECT ' ', 'SEED', AssociateID, PreferredLastName + ', ' + PreferredFirstName, Title, 0  
	FROM AssociateMaster 
	WHERE 
	AssociateID = 'aaaaaaaaa'
	AND ((@AssociateID = 'xxxxxxxxx' and Title <> 'Managing Director')	--filter not showing MD's 
	      OR  (@AssociateID <> 'xxxxxxxxx')) 

	ORDER BY 
	DR.EmployeeLevel ASC, 
	MGR.PreferredLastName + ', ' + MGR.PreferredFirstName ASC,
	DR.Associate ASC

RE: HR Recursive List using CTE but includes 'self' as level 0



Are you are looking to remove Managing Directors from the output for only user 'xxxxxxxxxxx' ?

CODE

AND 1 = case when (@Associate = 'xxxxxxxxxxxx' and title = 'Managing Director') then 0 else 1 end


Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

RE: HR Recursive List using CTE but includes 'self' as level 0

(OP)
Correct, I am looking to remove 'Managing Director' for only one Associate running the code, everyone else receives the full list.

Rob

RE: HR Recursive List using CTE but includes 'self' as level 0



You should be able to replace with:

CODE

SELECT 
    DR.ManagerID, MGR.PreferredLastName + ', ' + MGR.PreferredFirstName AS ManagerPrefName,
    DR.EmployeeID as AssociateID, DR.Associate as AssociatePrefName, DR.Title, DR.EmployeeLevel   
FROM #DirectReports DR
	INNER JOIN AssociateMaster as MGR ON DR.ManagerID = MGR.AssociateID
WHERE
    AssociateID = 'aaaaaaaaa'
    AND 1 = case when (@Associate = 'xxxxxxxxxxxx' and title = 'Managing Director') then 0 else 1 end

So even if the @AssociateID was "xxxxxxxxxxxx", the Managing Director row would still be excluded.


Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login
Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close