How to map out multi-level Supervisor/Employee relationship
-
No doubt this has already been answered and probably detailed many ways so I just need to know the right key words to find the post or a link if anyone has it.
I'm trying to write a query that will show all levels of Supervisor to Employee relations ships where the relationships can go several levels deep; employee reports to employee 10 who reports to employee 100 and so on. Unlike the referencing setup where 1 table has all the employees and a column in it links the employee to their supervisor I'm working with 2 tables; 1 for the employees called PERSON and another that has 1 row for each Employee to Supervisor relationship.
There is an existing query (see below) that will do this but its got a max of 4 levels so if there are more than 4 levels to this hierarchy it doesn't show every one. So this has been fine to use because we've not exceeded that many levels and I know I could append another copy of each table as the existing query does to cover another level but I'm looking for something that will work no matter how many levels they're are, something recursive I guess. I'd be great to if the query could also indicate what level each employee is for a specif department starting with @mySupervisorCode as shown below.
If the supervisor is 'IT0001' then the query should show every person that 'IT0001' is the Supervisor for directly or indirectly and go down until the last employee whom is not the supervisor of anyone is listed. If John reports to Sam and Sam reports to john and john reports to Larry and Larry reports to Max and Max reports to IT0001 than within this the employee John would be level 5 while the employee IT0001 would be at level 1.
I've also posted the DDL for these 2 tables but I have removed all superfluous columns (address, Name, ect) that aren't necessary to answer the question. The PERSON table is very wide and most of it has nothing to do with this relationship thing so I removed all the unnecessary columns.
SELECT PL.hMy hSupervisor,
PL.sCode sSuperCode,
PL.sDept sDept,
IsNull(P3.sCode,IsNull(P2.sCode,IsNull(P1.sCode,P.sCode))) sEmployeeCode,
IsNull(P3.hMy,IsNull(P2.hMy,IsNull(P1.hMy,P.hMy))) hEmployee
FROM PERSON PL Left Outer Join PERSONXREF X ON PL.hMy = X.hSupervisor
Left Outer Join PERSON P ON X.hPERSON = P.hMy
Left Outer Join PERSONXREF X1 ON X1.hSupervisor = P.hMy
Left Outer Join PERSON P1 ON X1.hPERSON = P1.hMy
Left Outer Join PERSONXREF X2 ON X2.hSupervisor = P1.hMy
Left Outer Join PERSON P2 ON X2.hPERSON = P2.hMy
Left Outer Join PERSONXREF X3 ON X3.hSupervisor = P2.hMy
Left Outer Join PERSON P3 ON X3.hPERSON = P3.hMy
WHERE PL.sCode = @mySupervisorCodeDDL:
CREATE TABLE dbo.PERSON ( hMy NUMERIC(18, 0) NOT NULL IDENTITY(1, 1),
sCode CHAR(8) NOT NULL,
sDept CHAR(8),
iType NUMERIC (18, 0) NULL
) ON [PRIMARY]
CREATE TABLE dbo.PERSONXREF ( hMy NUMERIC(18, 0) NOT NULL IDENTITY(1, 1),
hPerson NUMERIC(18, 0) NOT NULL,
hManager NUMERIC(18, 0) NULL
) ON [PRIMARY]- This topic was modified 3 years, 7 months ago by YSLGuru.
Kindest Regards,
Just say No to Facebook! -
No sample data so this is untested.
WITH Recur AS (
SELECT PL.hMy hSupervisor,
PL.sCode sSuperCode,
PL.sDept,
PL.hMy,
CAST(1 AS INT) AS Level
FROM PERSON PL
WHERE PL.sCode = @mySupervisorCode
UNION ALL
SELECT PL.hSupervisor,
PL.sSuperCode,
PL.sDept,
X.hMy,
PL.Level + 1
FROM Recur PL
INNER JOIN PERSONXREF X ON X.hManager = PL.hMy
)
SELECT r.hSupervisor,
r.sSuperCode,
r.sDept,
r.Level,
PL.sCode AS sEmployeeCode,
PL.hMy AS hEmployee
FROM Recur r
INNER JOIN PERSON PL ON PL.hMy = r.hMy;____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply