The full task sounds like this: Display the content of employees whose immediate supervisor is younger and less employed in the company.

Columns:

Manager Name | Date of hiring a manager | Head's date of birth 
Employee name | Employee hiring date | Employee's date of birth 

I already broke my head here:

SELECT
    LastName   ' '   FirstName AS SupervisorFullName,
    HireDate,
    BirthDate,
    (SELECT LastName   ' '   FirstName
     FROM HumanResources.Employee AS subHrE
     WHERE HrE.OrganizationNode.IsDescendantOf(subHrE.OrganizationNode) = 1 
       AND HrE.OrganizationLevel = HrE.OrganizationNode.GetLevel()   1) AS EmployeeFullName,
    (SELECT HireDate
     FROM HumanResources.Employee AS subHrE
     WHERE HrE.OrganizationNode.IsDescendantOf(subHrE.OrganizationNode) = 1 
       AND HrE.OrganizationLevel = HrE.OrganizationNode.GetLevel()   1) AS HireDateEmp,
    (SELECT BirthDate
     FROM HumanResources.Employee AS subHrE
     WHERE HrE.OrganizationNode.IsDescendantOf(subHrE.OrganizationNode) = 1 
       AND HrE.OrganizationLevel = HrE.OrganizationNode.GetLevel()   1) AS BithDateEmp
FROM 
    HumanResources.Employee as HrE
JOIN 
    Person.Person as P ON HrE.BusinessEntityID = P.BusinessEntityID
ORDER BY 
    SupervisorFullName ASC

Output

AdventureWork2016 db is used for work

Full Schema AW2016

CodePudding user response：

There's definitely a certain way to think about use of hierarchyid that, once you get the hang of it, opens a lot of possibilities. Here's what I came up with:

WITH FullPerson AS (
    SELECT CONCAT_WS(' ', p.FirstName, p.MiddleName, p.LastName) AS [FullName],
        e.HireDate,
        e.BirthDate,
        e.OrganizationNode
    FROM HumanResources.Employee AS e
    JOIN Person.Person AS p
        ON p.BusinessEntityID = e.BusinessEntityID
)
SELECT
    manager.OrganizationNode.ToString(),
    manager.FullName,
    manager.HireDate,
    manager.BirthDate,

    subordinate.OrganizationNode.ToString(), 
    subordinate.FullName,
    subordinate.HireDate,
    subordinate.BirthDate
FROM FullPerson AS subordinate
JOIN FullPerson AS manager
    ON subordinate.OrganizationNode.GetAncestor(1) = manager.OrganizationNode
WHERE manager.HireDate > subordinate.HireDate
    AND manager.BirthDate > subordinate.BirthDate;

Breaking it down, I create a common table expression to join Employee and Person as I'll need columns from both tables for both subordinates and their managers. The real trick is the join condition. subordinate.OrganizationNode.GetAncestor(1) = manager.OrganizationNode says "take the subordinate's OrganizationNode and go one level up the tree". What's amazing to me is that this sort of query can be supported by indexes and indeed there is an index on that column in the AdventureWorks schema! In addition to the columns you asked for, I added a human-readable representation of OrganizationNode to help with the visualization of how the data relates.