fucking.sql

declare @employee varchar(20)
set @employee = '055320';

WITH Employees (Employee, Level, Boss, Name, BTL) AS (
	SELECT
		1 AS Level,
		NULL AS Boss,
		EM.Employee,
		EM.LastName + ', ' + EM.PreferredName AS Name,
		BTL.LastName + ', ' + BTL.PreferredName AS BTL

	FROM
		GarverDB.dbo.EmployeeCustomTabFields
		LEFT JOIN GarverDB.dbo.EM EM ON EM.Employee = EmployeeCustomTabFields.Employee
		LEFT JOIN GarverDB.dbo.EM BLD ON BLD.Employee = EmployeeCustomTabFields.custBusinessLineDirector
		LEFT JOIN GarverDB.dbo.EM BTL ON BTL.Employee = EmployeeCustomTabFields.custBusinessTeamLeader

	WHERE
		EM.Status = 'A'
		AND EM.Employee != @employee
		AND EmployeeCustomTabFields.custBusinessTeamLeader = EM.Employee
		AND (
			@employee = EmployeeCustomTabFields.custBusinessTeamLeader
			OR @employee = EmployeeCustomTabFields.custBusinessLineDirector
		)
        UNION ALL
        SELECT
                boss.Level + 1 AS Level,
                boss.Employee AS Boss,
                EM.Employee,
		EM.LastName + ', ' + EM.PreferredName AS Name,
		BTL.LastName + ', ' + BTL.PreferredName AS BTL
	FROM
		GarverDB.dbo.EmployeeCustomTabFields
		LEFT JOIN GarverDB.dbo.EM EM ON EM.Employee = EmployeeCustomTabFields.Employee
		LEFT JOIN GarverDB.dbo.EM BLD ON BLD.Employee = EmployeeCustomTabFields.custBusinessLineDirector
		LEFT JOIN GarverDB.dbo.EM BTL ON BTL.Employee = EmployeeCustomTabFields.custBusinessTeamLeader
                INNER JOIN Employees boss ON boss.Employee = EM.EmployeeCustomTabFields.custBusinessTeamLeader
        WHERE
		EM.Status = 'A'
)

SELECT * FROM Employees