CTE organizational hierarchy needs base attached to recursive records.

I've attached code regarding a CTE which recursively begins with a small set of head supervisors and pulls back the hierarchy level and employees reporting to them throughout the hierarchy. What I would like to do is attach each reporting employee to each one of the beginning/base set of head supervisors. How may I complete this task?
--Recursive Query
WITH EVP_Hierarchy (EmployeeNo, EmployeeName, JobCode, JobTitle, DateHired, AdjustedHireDate, DateTermed, 
						TermCode, TermDesc, SupervisorID, Supervisor, DeptNo, Department, HierarchyLevel) 
AS
(
--Base Query
SELECT	TOP (100) PERCENT lawson_temp.employee_nbr AS EmployeeNo,
		lawson_temp.first_name + ' ' + lawson_temp.last_name AS EmployeeName,
		lawson_temp.job_code AS JobCode,
		lawson_temp.job_title AS JobTitle,
		lawson_temp.date_hire AS DateHired,
		lawson_temp.adj_hire_date AS AdjustedHireDate,
		lawson_temp.term_date AS DateTermed,
		lawson_temp.term_code AS TermCode,
		lawson_temp.term_descr AS TermDesc,
		lawson_temp.supervisor_id AS SupervisorID,
		lawson_temp.supervisor_name AS Supervisor,
		lawson_temp.dept_nbr AS DeptNo,
		lawson_temp.dept_name AS Department,
		0 AS Hierarchy
FROM	lawson_temp
WHERE	(lawson_temp.term_date IS NULL) AND 
		(lawson_temp.employee_nbr IN (SELECT employee_nbr FROM lawson_temp WHERE (job_title = 'Executive Vice President')))
ORDER BY	lawson_temp.employee_nbr


UNION ALL

--Recursive Step
SELECT	lawson_temp.employee_nbr AS EmployeeNo,
		lawson_temp.first_name + ' ' + lawson_temp.last_name AS EmployeeName,
		lawson_temp.job_code AS JobCode,
		lawson_temp.job_title AS JobTitle,
		lawson_temp.date_hire AS DateHired,
		lawson_temp.adj_hire_date AS AdjustedHireDate,
		lawson_temp.term_date AS DateTermed,
		lawson_temp.term_code AS TermCode,
		lawson_temp.term_descr AS TermDesc,
		lawson_temp.supervisor_id AS SupervisorID,
		lawson_temp.supervisor_name AS Supervisor,
		lawson_temp.dept_nbr AS DeptNo,
		lawson_temp.dept_name AS Department,
		EVP_Hierarchy.HierarchyLevel + 1 AS HierarchyLevel
FROM	(SELECT	TOP (100) PERCENT supervisor_id AS SupervisorID, 
				employee_nbr AS EmployeeNo 
		FROM	lawson_temp 
		WHERE	(supervisor_id IS NOT NULL) AND (lawson_temp.term_date IS NULL) 
		ORDER BY	supervisor_id, 
					EmployeeNo) AS Supervisors
		INNER JOIN lawson_temp ON Supervisors.EmployeeNo = lawson_temp.employee_nbr
		INNER JOIN EVP_Hierarchy ON Supervisors.SupervisorID = EVP_Hierarchy.EmployeeNo
WHERE	(lawson_temp.term_date IS NULL)
)
--Final Select
SELECT	TOP (100) PERCENT HierarchyLevel,
		EmployeeNo, 
		EmployeeName, 
		JobCode, 
		JobTitle, 
		DateHired, 
		AdjustedHireDate, 
		SupervisorID, 
		Supervisor, 
		DeptNo, 
		Department
FROM	EVP_Hierarchy

Open in new window

Levi MartinSenior Data AnalystAsked:
Who is Participating?
 
Kevin CrossConnect With a Mentor Chief Technology OfficerCommented:
HI.

Do you mean that you want to persist the top-level Supervisor throughout?

If so, amend your CTE definition to:

WITH EVP_Hierarchy (EVPEmployeeNo, EmployeeNo, ... )

Then for the first level, repeat the EmployeeNo:

SELECT      lawson_temp.employee_nbr AS EVPEmployeeNo, lawson_temp.employee_nbr AS EmployeeNo

Then in the recursive step, you can just keep pulling EVPEmployeeNo.

SELECT      EVP_Hierarchy.EVPEmployeeNo, lawson_temp.employee_nbr AS EmployeeNo

Hope that helps!

Kevin

P.S. I would remove the ORDER BY in the CTE and the TOP (100) PERCENT throughout. Do the ordering in the final selection.
0
 
Levi MartinSenior Data AnalystAuthor Commented:
Solution was complete, accurate, and informative. The answer was staring right back at me and is a lesson I will definitely carry with for further programming. Thank you so much!
0
All Courses

From novice to tech pro — start learning today.