How to get managers list

Posted on 2014-08-18
Medium Priority
Last Modified: 2014-08-18
Thank you in advance for any feedback you may provide.

On the adventureworks database provided by MS Office they have on the humanresources.employee a field named organizationnode.  Based on this field I would
like to get a list of all managers only.  Please present me with the code I'm not too familiar with sql.
Question by:rborda
LVL 23

Expert Comment

ID: 40267889
Select * from humanresources.employee
where organizationnode like '%managers%'

Were the humanresources.employee is the name of the table, organizationnode the name of the row and '%managers%' the phrase you are looking for in that row. the % are SQL wildcards.
LVL 36

Accepted Solution

ste5an earned 2000 total points
ID: 40267948
The OrganizationNode column is a hierarchyid. It's a compact data type to store hierarchy information in form of a path. This can be verifyed by using the ToString() method. btw, this data type is a CLR data type, thus ToString() is known method from classes in the .Net framework:

SELECT  E.BusinessEntityID ,
        E.NationalIDNumber ,
        E.OrganizationNode ,
        E.OrganizationLevel ,
        E.JobTitle ,
FROM    HumanResources.Employee E;

Open in new window

So now the question is: How do you define "manager" in conjunction to this hierarchy? Any none leaf? The necessary information is already present in the computed column OrganizationLevel which is OrganizationNode.GetLevel():

SELECT  E.BusinessEntityID ,
        E.NationalIDNumber ,
        E.OrganizationNode ,
        E.OrganizationLevel ,
FROM    HumanResources.Employee E
WHERE   E.OrganizationLevel < ( SELECT  MAX(L.OrganizationLevel)
                                FROM    HumanResources.Employee L

Open in new window

LVL 70

Expert Comment

by:Scott Pletcher
ID: 40268921
This certainly sounds like some type of classwork or homework.  If so, we can/should only provide general approaches/ideas, not full code.

Author Closing Comment

ID: 40269210
It returned more than the number of supervisors, but it put me on the right track.  Thank you.

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question