CREATE TABLE Employees
(EmployeeLevel hierarchyid NOT NULL,
Position nvarchar(60) NOT NULL,
JobType nvarchar(60) NULL);

INSERT employees
VALUES ('/', 'CEO', 'Executive');

INSERT Employees
VALUES
('/1/', 'CIO', 'IT Executive'),
('/2/', 'CFO', 'Finance Executive'),
('/1/1/', 'Project Manager', 'IT Manager'),
('/1/1/1/', 'Programmer', 'IT Staff'),
('/1/2/1/', 'Systems Engineer', 'IT Staff'),
('/1/2/', 'Infrastructure Manager', 'IT Manager'),
('/3/', 'CMO', 'Marketing Executive'),
('/2/1/', 'Accounts Rec Manager', 'Finance Manager'),
('/2/1/1/', 'Accountant', 'Finance Staff'),
('/2/1/2/', 'Accountant', 'Finance Staff'),
('/2/1/2/1/', 'Accountant Assistant', 'Finance Support Staff'),
('/3/1/', 'Direct Channels Marketing Manager', 'Marketing Manager');
 
select cast(employeelevel as nvarchar(100)) as [Converted Level]from employees

DECLARE @id as Hierarchyid
SET @id = (SELECT employeelevel from employees where Position = 'CFO')
SELECT * from employees
where employeelevel.IsDescendantOf (@id) =1

DECLARE @id as Hierarchyid
SET @id = (SELECT employeelevel from employees where Position = 'CEO')
SELECT * from employees
where employeelevel.IsDescendantOf (@id) =1