May 9, 2011 at 6:55 am
I'm trying to create a recursive cte that can select manager - employee next level down - employee next level down - employee next level down - etc in a heirarchical structure. My efforts below are close, but I can't seem to finish it, any help would be appreciated.
with EmployeeManagerRelationship (managerid, employeeid, employeelevel, [level], managercontactid, managertitle
, managerfirst, managerlast, employeetitle, employeefirst, employeelast, employeefirst2, employeelast2)
as
(
select e.managerid, e.employeeid, e.title as employeelevel, 0 as [level], e.contactid as managercontactid
, CAST(' ' as nvarchar(8)) as managertitle, CAST(' ' as nvarchar(50)) as managerfirst
, CAST(' ' as nvarchar(50)) as managerlast, c.title as employeetitle, c.firstname as employeefirst
, c.lastname as employeelast, c.firstname as employeefirst2, c.lastname as employeelast2
from HumanResources.Employee e
join Person.Contact c on e.ContactID = c.ContactID
where ManagerID is null
union all
select e.managerid, e.employeeid, e.title as employeelevel, [level] + 1, e.contactid as managercontactid
, m.title as managertitle, m.firstname as managerfirst, m.lastname as managerlast, c.title as employeetitle
, c.firstname as employeefirst, c.lastname as employeelast, f.firstname as employeefirst2
, f.lastname as employeelast2
from HumanResources.Employee e
join Person.Contact c on c.ContactID = e.ContactID
join EmployeeManagerRelationship d on d.employeeid = e.ManagerID
join Person.Contact m on m.ContactID = d.managercontactid
join Person.Contact f on f.ContactID = c.ContactID
)
select managerfirst + ' ' + managerlast as manager, employeefirst2 + ' ' + employeelast2 as secondlevel
, employeefirst + ' ' + employeelast as thirdlevel, employeefirst + ' ' + employeelast as fourthlevel
from EmployeeManagerRelationship
where managerfirst <> ' '
order by [level]
There is an exception to every rule, except this one...
May 9, 2011 at 8:43 am
May 9, 2011 at 12:32 pm
I've checked it out thoroughly, but still can't come up with the right query.
There is an exception to every rule, except this one...
May 9, 2011 at 12:37 pm
You may want to consider scripting out the tables and providing some dummy data.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 9, 2011 at 12:39 pm
Welsh Corgi (5/9/2011)
You may want to consider scripting out the tables and providing some dummy data.
I'm using the AdventureWords DB.
There is an exception to every rule, except this one...
May 9, 2011 at 7:03 pm
I believe you're trying to solve too many problems at the same time. "Divide'n'Conquer". Solve the hierarchy first then join the other tables to get the amplifying information.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 9, 2011 at 7:49 pm
Try this out... and, no... there's no dynamic SQL in it. The bloody forum software just makes it look that way...
USE AdventureWorks;
WITH
cteDirectReports AS
(
SELECT EmployeeID, ManagerID, EmployeeLevel = Title, [Level] = 0, ContactID,
HierarchicalPath = CAST('\'+CAST(EmployeeID AS VARCHAR(10)) AS VARCHAR(4000))
FROM HumanResources.Employee
WHERE ManagerID IS NULL
UNION ALL
SELECT e.EmployeeID, e.ManagerID, EmployeeLevel = e.Title, [Level] = d.[Level] + 1, e.ContactID,
HierarchicalPath = CAST(d.HierarchicalPath + '\'+CAST(e.EmployeeID AS VARCHAR(10)) AS VARCHAR(4000))
FROM HumanResources.Employee e
INNER JOIN cteDirectReports d ON e.ManagerID = d.EmployeeID
),
cteManagerInfo AS
(
SELECT dr.EmployeeID,
EmployeeFullName = c.FirstName+' '+c.LastName,
dr.ManagerID,
m.ContactID,
ManagerLevel = m.Title,
dr.EmployeeLevel,
dr.[Level],
dr.HierarchicalPath
FROM cteDirectReports dr
LEFT OUTER JOIN Person.Contact c ON dr.ContactID = c.ContactID
LEFT OUTER JOIN HumanResources.Employee m ON dr.ManagerID = m.EmployeeID
)
SELECT m.EmployeeID,
m.EmployeeFullName,
m.EmployeeLevel,
m.ManagerID,
ManagerFullName = c.FirstName+' '+c.LastName,
m.ManagerLevel,
m.[Level],
m.HierarchicalPath
FROM cteManagerInfo m
LEFT OUTER JOIN Person.Contact c ON m.ContactID = c.ContactID
ORDER BY HierarchicalPath
;
--Jeff Moden
Change is inevitable... Change for the better is not.
May 10, 2011 at 2:03 am
It appears that you're trying to flatten the parent child hierachy, I posted something similar here,
http://www.sqlservercentral.com/Forums/Topic1071035-392-1.aspx#bm1073147
Post 4 on the thread was the solution i finally implemented, It could be a good starting block, its not optimal and I have a couple of Ideas on how to improve it I just need to find the time to do them.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
May 10, 2011 at 7:11 am
Thanks for your responses! I have what I was looking for with your help.
There is an exception to every rule, except this one...
May 10, 2011 at 11:22 am
SQLHeap (5/10/2011)
Thanks for your responses! I have what I was looking for with your help.
Outstanding! When you get done, would you post your final solution here, please? Thanks. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply