title: Find all nodes that have children with hierarchyid
body: <p>Given this table:</p>
  
  <pre><code>CREATE TABLE Employee
  (
      EmpId INT PRIMARY KEY IDENTITY,
      EmpName VARCHAR(100) NOT NULL,
      Position HierarchyID NOT NULL
  )
  
  INSERT INTO Employee (EmpName, Position)
  VALUES ('CEO', '/'),
      ('COO', '/1/'),
      ('CIO', '/2/'),
      ('CFO', '/3/'),
      ('VP Financing', '/3/1/'),
      ('Accounts Receivable', '/3/1/1/'),
      ('Accountant 1', '/3/1/1/1/'),
      ('Accountant 2', '/3/1/1/2/'),
      ('Accountant 3', '/3/1/1/3/'),
      ('Accounts Payable', '/3/1/2/'),
      ('Accountant 4', '/3/1/2/1/'),
      ('Accountant 5', '/3/1/2/2/'),
      ('DBA', '/2/1/'),
      ('VP of Operations', '/1/1/')
  </code></pre>
  
  <p>How do I find all the rows that don't have any child nodes? </p>
  
  <p>I have the following that seems to work, but it seems like there should be a less convoluted way:</p>
  
  <pre><code>select * from (
      select 
          *,  
          case 
              when (select top 1 e.Position from dbo.Employee e where Position.GetAncestor(1) = Employee.Position) is null then 
                  cast (0 as bit)
              else 
                  cast (1 as bit)
          end as HasDescendants     
      from 
          dbo.Employee
  ) as managers
  where HasDescendants = 0
  </code></pre>