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
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
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
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
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
THANK YOU!!
Rob
RE: HR Recursive List using CTE but includes 'self' as level 0
Code I have so far:
CODE --> TSQL
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
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
Rob
RE: HR Recursive List using CTE but includes 'self' as level 0
You should be able to replace with:
CODE
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