I have a typical self-referencing employee table. The data is hierarchical and I use the following UDF to encapsulate a simple CTE to derive an output of a particular person's direct and indirect reports. That is to say I can pass in a single employee and get back a listing of them and everyone under them.
I want to create a similar function that would allow me to take a boss and employee param and detect if the employee reports into the boss (or the boss param is indeed the boss - direct or indirect of the employee)
whats the best way to accomplish this logic using the DB structure outlined below as what I have today?
CREATE TABLE [dbo].[Employees]( [EmployeeId] [int] NOT NULL, [managerId] [int] NULL, [FirstName] [nvarchar](50) NOT NULL, [LastName] [nvarchar](50) NOT NULL, [FullName] [nvarchar](100) NOT NULL, [CorpEmailAddress] [nvarchar](510) NULL ) ON [PRIMARY] ALTER FUNCTION [dbo].[fnGetEmployeeHierarchy] ( @EmployeeId int = null ) RETURNS TABLE AS RETURN ( WITH yourcte AS ( SELECT EmployeeId, ManagerID, AMRSNTID, FullName--, Name FROM Employees WHERE EmployeeId = isnull(@EmployeeId,EmployeeId) UNION ALL SELECT e.EmployeeId, e.ManagerID, e.AMRSNTID, e.FullName--, e.Name FROM Employees e JOIN yourcte y ON e.ManagerID = y.EmployeeId ) SELECT EmployeeId, ManagerID, AMRSNTID, FullName--, Name FROM yourcte )