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 )
If you just want to return a true or false upon comparing an employee and a manager, try this:
CREATE FUNCTION [dbo].[fnGetEmployeeHierarchy] ( @EmployeeId INT, @ManagerId INT ) RETURNS BIT AS BEGIN DECLARE @return BIT; SET @return = 0; WITH yourcte AS ( SELECT EmployeeId, ManagerID, FullName FROM Employees WHERE EmployeeId = @EmployeeId UNION ALL SELECT e.EmployeeId, e.ManagerID, e.FullName FROM Employees e INNER JOIN yourcte y ON e.EmployeeId = y.ManagerId ) SELECT @return = 1 FROM yourcte WHERE EmployeeId = @ManagerId; RETURN @return; END
Since to me it’s already a well known and solved problem, and since the commentary on here seems to agree with me:
Are you wanting to return the results for each lowest employee in the set, and that’s what’s making it hard?