November 5, 2018 at 12:37 am
I have following as example:
CREATE TABLE dbo.MyEmployees
(
EmployeeID smallint NOT NULL,
FirstName nvarchar(30) NOT NULL,
LastName nvarchar(40) NOT NULL,
Title nvarchar(50) NOT NULL,
DeptID smallint NOT NULL,
ManagerID int NULL,
CONSTRAINT PK_EmployeeID PRIMARY KEY CLUSTERED (EmployeeID ASC)
);
-- Populate the table with values.
INSERT INTO dbo.MyEmployees VALUES
(1, N'Ken', N'Sánchez', N'Chief Executive Officer',16,NULL)
,(273, N'Brian', N'Welcker', N'Vice President of Sales',3,1)
,(274, N'Stephen', N'Jiang', N'North American Sales Manager',3,273)
,(275, N'Michael', N'Blythe', N'Sales Representative',3,274)
,(276, N'Linda', N'Mitchell', N'Sales Representative',3,274)
,(285, N'Syed', N'Abbas', N'Pacific Sales Manager',3,273)
,(286, N'Lynn', N'Tsoflias', N'Sales Representative',3,285)
,(16, N'David',N'Bradley', N'Marketing Manager', 4, 273)
,(23, N'Mary', N'Gibson', N'Marketing Specialist', 4, 16);
select * from MyEmployees
WITH DirectReports (ManagerID, EmployeeID, Title, DeptID, Level)
AS
(
-- Anchor member definition
SELECT e.ManagerID, e.EmployeeID, e.Title, e.DeptID,--edh.DepartmentID,
0 AS Level
FROM dbo.MyEmployees AS e
--INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
-- ON e.EmployeeID = edh.BusinessEntityID AND edh.EndDate IS NULL
WHERE ManagerID IS NULL
UNION ALL
-- Recursive member definition
SELECT e.ManagerID, e.EmployeeID, e.Title, e.DeptID,--edh.DepartmentID,
Level + 1
FROM dbo.MyEmployees AS e
--INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
-- ON e.EmployeeID = edh.BusinessEntityID AND edh.EndDate IS NULL
INNER JOIN DirectReports AS d
ON e.ManagerID = d.EmployeeID
)
-- Statement that executes the CTE
SELECT ManagerID, EmployeeID, Title, DeptID, Level
FROM DirectReports
--INNER JOIN HumanResources.Department AS dp
-- ON DirectReports.DeptID = dp.DepartmentID
--WHERE -- dp.GroupName = N'Sales and Marketing' OR
--Level = 1;
GO
I can find the level, but I want to fill a user class with his immediate boss and all up level Managers who can update his profile:
;
with EMP (Id, FirstName, LastName, IsActive, ApprovingAuthorityId, Level) as
(
select e.Id, FirstName, LastName, IsActive, ApprovingAuthorityId, 0 as level from Attendance.Employees e inner join Attendance.ApprovingAuthorities a on a.EmployeeId = e.Id
where a.ApprovingAuthorityId = 0
UNION ALL
select e.Id, e.FirstName, e.LastName, e.IsActive, e.ApprovingAuthorityId, Level+1 from
(select e.Id, FirstName, LastName, IsActive, ApprovingAuthorityId from Attendance.Employees e inner join Attendance.ApprovingAuthorities a on a.EmployeeId = e.Id) as e
inner join EMP ct on ct.Id = e.ApprovingAuthorityId
)
select c.Id, c.FirstName, c.LastName, c.IsActive, ApprovingAuthorityId, e.FirstName + ' ' + e.LastName Manager, Level from EMP c inner join Attendance.Employees e on e.Id = c.ApprovingAuthorityId
order by Level
GO
I also want a list to fill all emp Ids to which a user can Manager (those employees who lies under current logged in user). Lets say a employee login to website, I want to know this user's immediate manager, all up level managers and those employees list to which current employee can manager.
November 7, 2018 at 3:22 pm
The code below might help get the list of up level managers. I'm not sure how to easily get the list of employees each person manages...
WITH DirectReports (ManagerID, EmployeeID, Title, DeptID, Level, ManagerList)
AS
(
-- Anchor member definition
SELECT e.ManagerID, e.EmployeeID, e.Title, e.DeptID,
0 AS Level, TRY_CAST('Self' AS NVARCHAR(500)) AS ManagerList
FROM dbo.MyEmployees AS e
WHERE ManagerID IS NULL
UNION ALL
-- Recursive member definition
SELECT e.ManagerID, e.EmployeeID, e.Title, e.DeptID,
Level + 1, TRY_CAST(CONCAT(d.Title, ', ', d.ManagerList) AS NVARCHAR(500)) AS ManagerList
FROM dbo.MyEmployees AS e
INNER JOIN DirectReports AS d
ON e.ManagerID = d.EmployeeID
)
-- Statement that executes the CTE
SELECT ManagerID, EmployeeID, Title, DeptID, Level, ManagerList
FROM DirectReports
GO
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply