CREATE 
--ALTER
FUNCTION AWEmpAncestors(@startAt INT)
RETURNS TABLE
RETURN
WITH Rollup
AS
(
SELECT EmployeeID, ManagerID, 0 AS distance
	FROM AdventureWorks.HumanResources.Employee
	WHERE EmployeeID = @startAt
UNION ALL
SELECT E.EmployeeID, E.ManagerID, 
	distance + 1 AS distance
	FROM AdventureWorks.HumanResources.Employee E
JOIN 
	Rollup AS R ON E.EmployeeID = R.ManagerID 
)
SELECT EmployeeID, ManagerID, distance FROM Rollup



CREATE 
--ALTER
FUNCTION AWEmpTree(@startAt INT)
RETURNS TABLE
RETURN
WITH Rollup AS (
SELECT EmployeeID, ManagerID,
	0 AS depth
	FROM AdventureWorks.HumanResources.Employee
	WHERE EmployeeID = @startAt
UNION ALL
SELECT E.EmployeeID,
	E.ManagerID, depth + 1 AS depth
	FROM AdventureWorks.HumanResources.Employee E
	JOIN
	Rollup AS R ON R.EmployeeID = E.ManagerID 
)
SELECT EmployeeID, ManagerID, depth FROM Rollup

select * from dbo.AWEmpTree(273)


select * from dbo.AWEmpAncestors(287)

