Parent Child with Hierarchy Path

;WITH cte AS (
    SELECT
      EmployeeKey
    , FirstName + ' ' + LastName EmployeeName
    , ParentEmployeeKey
    , 1 EmployeeLevel
    , CAST(EmployeeKey as varchar(max)) as  HierarchyPath
    FROM dbo.DimEmployee
    WHERE ParentEmployeeKey IS NULL
    and EndDate IS NULL
    UNION ALL
    SELECT
      c.EmployeeKey
    , c.FirstName + ' ' + c.LastName
    , c.ParentEmployeeKey
    , p.EmployeeLevel + 1
    , p.HierarchyPath + '\' + cast(c.EmployeeKey as varchar(max)) as  HierarchyPath
    FROM dbo.DimEmployee c
    JOIN cte p ON p.EmployeeKey = c.ParentEmployeeKey
    where EndDate IS NULL
)
SELECT
      cte.EmployeeName
    , cte.EmployeeLevel
    , HierarchyPath
    , cte.EmployeeKey
    , cte.ParentEmployeeKey
    , case when sum(isnull(f.SalesAmount,0)) <> 0 then cast(1 as bit) else cast(0 as bit) end as HasFactRecords
 FROM cte
LEFT JOIN dbo.FactResellerSales f
  ON f.EmployeeKey = cte.EmployeeKey
  group by   cte.EmployeeName, cte.EmployeeLevel , HierarchyPath     , cte.EmployeeKey ,cte.ParentEmployeeKey
ORDER BY HierarchyPath, EmployeeName






Till Next Time