July 14, 2014 at 9:10 am
Hi,
I need to get a hierarchy where manager gets to see all employees working under him and all employees working under his direct reports.
CREATE TABLE #managerEmployee (managerID INT,Manager VARCHAR(255),EmployeeID INT,Employee VARCHAR(255),Dept VARCHAR(25),LEVEL INT)
INSERT INTO #managerEmployee
( managerID ,
Manager ,
EmployeeID ,
Employee ,
Dept ,
LEVEL
)
SELECT 0,'na\SobeSobb',1,'na\Genaglov','Corporate G&A',0
UNION
SELECT 0,'na\SobeSobb',2,'na\TeniSrne','Admin G&A',0
UNION
SELECT 0,'na\SobeSobb',3,'na\johnadam','Aceca G&A',0
UNION
SELECT 0,'na\SobeSobb',4,'na\julicolw','PDS Processing G&A',0
UNION
SELECT 0,'na\SobeSobb',5,'na\SobeSobb','Corporate G&A',0
UNION
SELECT 0,'na\SobeSobb',6,'na\PodsGtrr','NSA GOM',0
UNION
SELECT 0,'na\SobeSobb',7,'na\tanapool','Legal G&A',0
UNION
SELECT 0,'na\SobeSobb',8,'na\WhiminYi','PDS Processing G&A',0
UNION
SELECT 145,'na\WhiminYi',718,'na\grazgrec','Processing Arcis',4
UNION
SELECT 145,'na\WhiminYi',719,'na\garyrodr','IMG Proc G&A',4
UNION
SELECT 145,'na\WhiminYi',720,'na\georclou','PDS Processing G&A',4
UNION
SELECT 145,'na\WhiminYi',721,'na\henrroen','IMG Proc G&A',4
UNION
SELECT 145,'na\WhiminYi',722,'na\jamehowe','IMG Proc G&A',4
UNION
SELECT 145,'na\WhiminYi',723,'na\GaoTuynh','PDS Processing G&A',4
UNION
SELECT 145,'na\WhiminYi',724,'na\binwangx','IMG R&D G&A',4
UNION
SELECT 145,'na\WhiminYi',725,'na\siermccl','PDS Processing G&A',4
UNION
SELECT 145,'na\WhiminYi',726,'na\terrhart','IMG Proc G&A',4
UNION
SELECT 46,'na\grazgrec',215,'na\wendohlh','IMG G&A Arcis',3
UNION
SELECT 46,'na\grazgrec',216,'na\satichop','Processing Arcis',3
UNION
SELECT 46,'na\grazgrec',217,'na\thanmcka','G&A Arcis',3
UNION
SELECT 46,'na\grazgrec',218,'na\petecary','Processing Arcis',3
UNION
SELECT 46,'na\grazgrec',219,'na\mikeperz','Processing Arcis',3
UNION
SELECT 46,'na\grazgrec',220,'na\mikestev','IMG G&A Arcis',3
UNION
SELECT 46,'na\grazgrec',221,'na\juanarev','IMG G&A Arcis',3
UNION
SELECT 46,'na\grazgrec',222,'na\eugebeso','IMG G&A Arcis',3
UNION
SELECT 46,'na\grazgrec',223,'na\debobarr','IMG G&A Arcis',3
SELECT * FROM #managerEmployee
DROP TABLE #managerEmployee
FOR na\WhiminYi , he should be able TO VIEW ALL employees AND ALL departments working under him and employees working under his direct reports AND including himself
na\grazgrec
na\garyrodr
na\georclou
na\henrroen
na\jamehowe
na\GaoTuynh
na\binwangx
na\siermccl
na\terrhart
i.e
SELECT 145AS ManagerID,'na\WhiminYi' AS Manager,718 AS EmployeeID,'na\grazgrec' AS Employee,'Processing Arcis' AS Department,4 AS LEVEL
UNION
SELECT 145AS ManagerID,'na\WhiminYi'AS Manager,719AS EmployeeID,'na\garyrodr' AS Employee,'IMG Proc G&A'AS Department,4 AS Level
UNION
SELECT 145AS ManagerID,'na\WhiminYi' AS Manager,720AS EmployeeID,'na\georclou' AS Employee,'PDS Processing G&A' AS Department,4 ASLEVELl
UNION
SELECT 145AS ManagerID,'na\WhiminYi' AS Manager,721AS EmployeeID,'na\henrroen' AS Employee,'IMG Proc G&A' AS Department,4 AS LEVEL
UNION
SELECT 145AS ManagerID,'na\WhiminYi' AS Manager,722AS EmployeeID,'na\jamehowe'AS Employee,'IMG Proc G&A' AS Department,4 AS LEVEL
UNION
SELECT 145 AS ManagerID,'na\WhiminYi'AS Manager,723 AS EmployeeID,'na\GaoTuynh'AS Employee,'PDS Processing G&A' AS Department,4 AS LEVEL
UNION
SELECT 145AS ManagerID,'na\WhiminYi' AS Manager,724 AS EmployeeID,'na\binwangx' AS Employee,'IMG R&D G&A' AS Department,4 AS LEVEL
UNION
SELECT 145AS ManagerID,'na\WhiminYi' AS Manager,725AS EmployeeID,'na\siermccl' AS Employee,'PDS Processing G&A'AS Department,4 AS Level
UNION
SELECT 145AS ManagerID,'na\WhiminYi' AS Manager,726AS EmployeeID,'na\terrhart' AS Employee,'IMG Proc G&A' AS Department,4 AS Level
UNION
SELECT 46AS ManagerID,'na\WhiminYi',215 AS EmployeeID,'na\wendohlh'AS Employee,'IMG G&A Arcis' AS Department,3 AS Level
UNION
SELECT 46AS ManagerID,'na\WhiminYi'AS Manager ,216AS EmployeeID,'na\satichop' AS Employee,'Processing Arcis' AS Department,3 ASLEVELl
UNION
SELECT 46AS ManagerID,'na\WhiminYi' AS Manager,217AS EmployeeID,'na\thanmcka'AS Employee,'G&A Arcis'AS Department,3 AS LEVEL
UNION
SELECT 46AS ManagerID,'na\WhiminYi' AS Manager,218AS EmployeeID,'na\petecary' AS Employee,'Processing Arcis' AS Department,3 AS Level
UNION
SELECT 46AS ManagerID,'na\WhiminYi'AS Manager,219AS EmployeeID,'na\mikeperz' AS Employee,'Processing Arcis'AS Department ,3 AS Level
UNION
SELECT 46AS ManagerID,'na\WhiminYi' AS Manager,220AS EmployeeID,'na\mikestev' AS Employee,'IMG G&A Arcis' AS Department,3 AS LEVEL
UNION
SELECT 46AS ManagerID,'na\WhiminYi'AS Manager,221AS EmployeeID,'na\juanarev'AS Employee,'IMG G&A Arcis' AS Department,3 AS Level
UNION
SELECT 46AS ManagerID,'na\WhiminYi' AS Manager,222AS EmployeeID,'na\eugebeso' AS Employee,'IMG G&A Arcis' AS Department,3 AS LEVEL
UNION
SELECT 46AS ManagerID,'na\WhiminYi' AS Manager,223AS EmployeeID,'na\debobarr'AS Employee,'IMG G&A Arcis' AS Department,3 AS LEVEL
UNION
SELECT 46AS ManagerID,'na\WhiminYi' AS Manager,223AS EmployeeID,'na\WhiminYi'AS Employee,'PDS Processing G&A' AS Department,0 AS LEVEL
Same FOR CEO 'na\SobeSobb', he should be able TO VIEW ALL employees AND ALL departments working under him and employees working under his direct reports AND including himself
na\Genaglov
na\TeniSrne
na\johnadam
na\julicolw
na\SobeSobb
na\PodsGtrr
na\tanapool
na\WhiminYi
Thanks,
PSB
July 14, 2014 at 10:03 am
so if you create the standard hierarchy and get a level, filter for the difference in Levels = 2 for the non-CEO types, and for the CEO, set the levels to maybe 20...?
July 14, 2014 at 10:14 am
You're missing information. There's no way to relate employees with managers in the sample data posted.
July 18, 2014 at 3:16 am
The manager ID should be related to an Employee ID to create the hierarchy chain. Please provide some good sampledata first 🙂
July 18, 2014 at 3:51 am
This will probably help you out and it details how to get the hierarchy out of the AdventureWorks database for Manger vs Employee
http://technet.microsoft.com/en-us/library/ms186243(v=sql.105).aspx
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply