Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
flyrplnz
Starting Member
6 Posts |
 Posted - 2006-04-06 : 17:11:20
|
I have an employee table which houses all employees and the employee to manager relationship. Such as the following.EmployeeIdFirstNameLastNameManagerIdIsManagerContained is about four levels of employees that report to managers on up to the top dog  . A subordinate of a manager would have the manager's EmployeeId value in their ManagerId column.How do I run a query to count the total number of employees each level of management have, not just their direct reports but including all indirect reports as well? In other words the top dog's count should include every record in the table including his own (because I need to know how many folks are on the team).I'm stumped |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2006-04-06 : 17:22:48
|
| The best approach depends on the version of SQL Server you are running. If you have SQL 2005, you can use Recursive Common Table Expressions, otherwise you'll need some more elaborate code. You might want to search here on trees and hierarchies. Or this article might help.---------------------------EmeraldCityDomains.com |
 |
|
|
flyrplnz
Starting Member
6 Posts |
Posted - 2006-04-06 : 17:35:09
|
Not on 2005 yet. Guess its the hard way.However I forgot to mention another column which is called Leaf which contains a hierarchical number that helps determine a managers direct reports. It would look something like this:Bob is a manager and he is the top dog. His Leaf value is 00001Sally reports to Bob and her Leaf value is 00001.00001Jerry also reports to Bob and his Leaf value is 00001.00002Jerry has a direct report named Mike and his Leaf value is 00001.00002.00001See how it works?I tried to figure a way to utilize the Leaf column but it got more confusing in my head. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
|
|
|
Cumulative count