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

[03Explorer]

I am using a single table

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

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

 

[MarkSweetland]

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)

declare @LoggedOnEmployeeId int = 2

;
WITH DirectReports(ManagerID, EmployeeID, Associate, Title, EmployeeLevel) AS   
(  
    SELECT ManagerID, UniqueID, LastName + ', ' + FirstName AS AssociateName, Title, [red]1[/red] 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
[blue]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[/blue]

  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

 

[03Explorer]

@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

 

[03Explorer]

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:

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

 

[MarkSweetland]

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

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

 

[03Explorer]

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

Rob

 

[MarkSweetland]

You should be able to replace with:

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