Recursive Common Table Expresson and Hierarchy Example

USE [TEST]
GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ManagerEmployee_EmployeeID]') AND parent_object_id = OBJECT_ID(N'[dbo].[Employee]'))
ALTER TABLE [dbo].[Employee] DROP CONSTRAINT [FK_ManagerEmployee_EmployeeID]
GO

USE [TEST]
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Employee]') AND type in (N'U'))
DROP TABLE [dbo].[Employee]
GO

USE [TEST]
GO


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Employee](
    [EmployeeID] [int] NOT NULL,
    [ManagerEmployeeID] [int] NULL,
    [EmpoyeeName] [varchar](50) NOT NULL,
    [Percentage] decimal(6,4) NOT NULL
 CONSTRAINT [PK_Employee_EmployeeID] PRIMARY KEY CLUSTERED
(
    [EmployeeID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Employee]  WITH CHECK ADD  CONSTRAINT [FK_ManagerEmployee_EmployeeID] FOREIGN KEY([ManagerEmployeeID])
REFERENCES [dbo].[Employee] ([EmployeeID])
GO

ALTER TABLE [dbo].[Employee] CHECK CONSTRAINT [FK_ManagerEmployee_EmployeeID]
GO

set nocount on

INSERT [dbo].[Employee] ([EmployeeID], [ManagerEmployeeID], [EmpoyeeName], [Percentage]) VALUES (1, NULL, N'CEO/Owner', 1.00)
INSERT [dbo].[Employee] ([EmployeeID], [ManagerEmployeeID], [EmpoyeeName], [Percentage]) VALUES (2, 1, N'Director 001', 0.3)
INSERT [dbo].[Employee] ([EmployeeID], [ManagerEmployeeID], [EmpoyeeName], [Percentage]) VALUES (3, 1, N'Director 002', 0.7)
INSERT [dbo].[Employee] ([EmployeeID], [ManagerEmployeeID], [EmpoyeeName], [Percentage]) VALUES (4, 2, N'Manager 001: Report to Director 001', 0.4)
INSERT [dbo].[Employee] ([EmployeeID], [ManagerEmployeeID], [EmpoyeeName], [Percentage]) VALUES (5, 2, N'Manager 002: Report to Director 001', 0.6)
INSERT [dbo].[Employee] ([EmployeeID], [ManagerEmployeeID], [EmpoyeeName], [Percentage]) VALUES (6, 3, N'Manager 003: Report to Director 002', 0.1)
INSERT [dbo].[Employee] ([EmployeeID], [ManagerEmployeeID], [EmpoyeeName], [Percentage]) VALUES (7, 3, N'Manager 004: Report to Director 002', 0.9)
INSERT [dbo].[Employee] ([EmployeeID], [ManagerEmployeeID], [EmpoyeeName], [Percentage]) VALUES (8, 4, N'Employee 001: Report to Manager 001',1)
INSERT [dbo].[Employee] ([EmployeeID], [ManagerEmployeeID], [EmpoyeeName], [Percentage]) VALUES (9, 5, N'Employee 002: Report to Manager 002',1)
INSERT [dbo].[Employee] ([EmployeeID], [ManagerEmployeeID], [EmpoyeeName], [Percentage]) VALUES (10, 6, N'Employee 003: Report to Manager 003',1)
INSERT [dbo].[Employee] ([EmployeeID], [ManagerEmployeeID], [EmpoyeeName], [Percentage]) VALUES (11, 7, N'Employee 004: Report to Manager 004',0.75)
INSERT [dbo].[Employee] ([EmployeeID], [ManagerEmployeeID], [EmpoyeeName], [Percentage]) VALUES (12, 7, N'Employee 005: Report to Manager 004',0.25)
GO


SELECT [EmployeeID]
      ,[ManagerEmployeeID]
      ,[EmpoyeeName]
      ,[Percentage]
  FROM [dbo].[Employee]
  ORDER BY 2,1

GO

with EmployeeHierarchy
    ([EmployeeID]
    ,[EmplyeeRank]
    ,[ManagerEmployeeID]
    ,[EmpoyeeName]
    ,[Percentage]
    )
as
(
SELECT [EmployeeID]
      ,1 As [EmplyeeRank]
      ,[ManagerEmployeeID]
      ,[EmpoyeeName]
      ,ISNULL([Percentage], 1)
  FROM [TEST].[dbo].[Employee]
  WHERE [ManagerEmployeeID] IS NULL
UNION ALL
SELECT E.[EmployeeID]
      ,1+EH.EmplyeeRank As [EmplyeeRank]
      ,E.[ManagerEmployeeID]
      ,E.[EmpoyeeName]
      ,CAST(ISNULL(E.[Percentage], 1)*EH.[Percentage] AS DECIMAL(6,4))
  FROM    EmployeeHierarchy EH INNER JOIN [TEST].[dbo].[Employee] E
        ON EH.EmployeeID=E.ManagerEmployeeID
)


SELECT
     [EmployeeID]
      ,[EmplyeeRank]
      ,[ManagerEmployeeID]
      ,[Percentage]
      ,[EmpoyeeName]
      FROM EmployeeHierarchy
      --WHERE [EmplyeeRank]=4
      order by 2,3,1;
GO