I have two tables: EmployeeMaster and EmployeeDetails. The schema of both are as below:
Sample data in both tables is shown:
I want to generate the hierarchy using EmployeeDetails table primarily. This table contains a column named: Manager. The EmployeeId of the Manager needs to be picked from the table EmployeeMaster table.
This is how the hierarchy needs to be formed. An EmployeeId is passed as a parameter to a stored procedure. The two supervisors of this Employee needs to be picked and 10 employees below this employee in seniority needs to be picked.
For instance, I pass the EmployeeId of Josh.Berkus to the stored procedure. The stored procedure query should return hierarchy as below:
I want the final output in this format:
Employee_Id ..... Manager_Id
Please note that Manager_Id is the EmployeeId of Manager.
I tried using a CTE with union all query, but not able to get it correctly.