Organizing data into hierarchical structure with SQL Server

I have a SQL query that I used with SSRS for creating a hierarchical employee report. SSRS had the ability to have recursive parent groupings, which explains the following table structure of the data.

This is how I organized my hierarchical employee list table:

EmpNTID​

name​

RMNTID​

TopManagerEmpNTID​

Level
1000​

Carmen​

1000​

0
1001​

Annette​

1000​

1000​

1
1002​

Shireen​

1000​

1000​

1
1003​

Jeffrey​

1000​

1000​

1
1004​

Eric​

1001​

1000​

2
1005​

Alton​

1002​

1000​

2
1006​

John​

1002​

1000​

2
1007​

Lynn​

1003​

1000​

2
1008​

Abhishek​

1003​

1000​

2
1009​

Alan​

1004​

1000​

3
1010​

Courtney​

1004​

1000​

3
1011​

Robert​

1004​

1000​

3
1012​

Michael​

1004​

1000​

3​

This is how I create my employee table:

BEGIN

DECLARE @ReportingDate date = GETDATE()
SET @ReportingDate = DATEADD(day, -1, @ReportingDate)

DECLARE @EmployeeTopMangerList TABLE
(
RowID int IDENTITY (1, 1),
[emplid] varchar(255),
EmpNTID varchar(255),
[name] varchar(255),
TopManagerEmplID varchar(255),
TopManagerEmpNTID varchar(255),
TopManagerName varchar(255)
)

---*****************add in loop
DECLARE @Employees TABLE
(
rowid int IDENTITY (1, 1),
EmpNTID varchar(255)
)

INSERT INTO @Employees
SELECT DISTINCT NTID
FROM [dbo].DAILY_ACTIVE_RFCS_RPT
WHERE ACTL_PCKG_RLS_DATE >= DATEADD(month, -8, DATEADD(DAY, 1, EOMONTH(@ReportingDate, 0)))

-- Get the number of rows in the looping table
DECLARE @RowCount INT
SET @RowCount = (SELECT COUNT(ROWID) FROM @Employees)

-- Declare an iterator
DECLARE @I INT

-- Initialize the iterator
SET @I = 1

-- Loop through the rows of a table @myTable
WHILE (@I <= @RowCount)
BEGIN
-- Declare variables to hold the data which we get after looping each record
DECLARE @iEmpNTID varchar(max), @iEmplID varchar(max)
DECLARE @TM_Name varchar(200), @TM_EmplID varchar(25), @TM_EmpNTID varchar(25)

-- Get the data from table and set to variables
SELECT @iEmpNTID = EmpNTID
FROM @Employees
WHERE ROWID = @I;

WITH Hierarchy([emplid],EmpNTID, [name], RMID, HLevel) AS
(
SELECT
[emplid],
EmpNTID,
[name],
RMID,
0 AS HLevel
FROM
vw_Employees_Combined
WHERE
-- EmplID = @iEmplID
EmpNTID = @iEmpNTID

UNION ALL

SELECT
SubDepartment.[emplid],
SubDepartment.EmpNTID,
SubDepartment.[name],
SubDepartment.RMID,
HLevel + 1
FROM
vw_Employees_Combined SubDepartment
INNER JOIN
Hierarchy ParentDepartment ON SubDepartment.[emplid] = ParentDepartment.RMID
)
SELECT TOP 1
@TM_EmplID = [emplid],
@TM_EmpNTID = EmpNTID,
@TM_Name = [name]
FROM
Hierarchy
ORDER BY
HLevel DESC

INSERT INTO @EmployeeTopMangerList ([emplid], EmpNTID,[name], TopManagerEmplID, TopManagerEmpNTID, TopManagerName)
SELECT
[emplid], EmpNTID, [name], @TM_EmplID, @TM_EmpNTID, @TM_Name
FROM
vw_Employees_Combined
WHERE
EmpNTID = @iEmpNTID -- EmplID = @iEmplID

-- -- Increment the iterator
SET @I = @I + 1
END
----************************

INSERT INTO @EmployeeTopMangerList ([emplid],EmpNTID,[name],TopManagerEmplID, TopManagerEmpNTID,TopManagerName )
SELECT DISTCINT
a.NTID, a.NTID, a.assgn, '000000', 'Unknown', ' Unknown Manager'
FROM
[dbo].DAILY_ACTIVE_RFCS_RPT a
WHERE
a.NTID NOT IN (SELECT EmpNTID FROM @EmployeeTopMangerList)

DECLARE @TopManagersTable TABLE
(
rowid int IDENTITY(1,1),
TopManagerEmplID varchar(255),
TopManagerEmpNTID varchar(255),
TopManagerName varchar(255)
)

INSERT INTO @TopManagersTable (TopManagerEmplID, TopManagerEmpNTID, TopManagerName)
SELECT DISTINCT
TopManagerEmplID, TopManagerEmpNTID, TopManagerName
FROM
@EmployeeTopMangerList

declare @EmployeeList table(
RowID int identity (1,1),
EmplID varchar(255),
EmpNTID varchar(255),
[name] varchar(255),
RMID varchar(255),
TopManagerEmplID varchar(255)
);

--------------------------------------------------------------------------
SET @RowCount = (SELECT COUNT(ROWID) FROM @TopManagersTable)

-- Declare an iterator
-- Initialize the iterator
SET @I = 1

-- Loop through the rows of a table @myTable
WHILE (@I <= @RowCount)
BEGIN
-- Declare variables to hold the data which we get after looping each record
-- Get the data from table and set to variables
SELECT @iEmpNTID = TopManagerEmpNTID FROM @TopManagersTable WHERE ROWID = @I;


with src as (
select
[emplid]
,EmpNTID
, [name]
, case
when [emplid] = RMID then null --@CurrentUserEmplID then null--[supervisor_id] then null
else RMID
end as RMID
from vw_Employees_Combined
), cte as (
select [emplid],EmpNTID, [name], RMID, [emplid] as top_manager
from src
where EmpNTID = @iEmpNTID --1 -- this is the manager you're interested in
-- it can be a sub-manager in the middle or the tree
union all -- or the top level manager rooting the tree

select src.[emplid],src.EmpNTID, src.[name], src.RMID, top_manager
from src
inner join cte on src.RMID = cte.[emplid]
where src.[emplid] <> top_manager
)
insert into @EmployeeList
select * from cte;

-- -- Increment the iterator
SET @I = @I + 1

END
----************************
------------------------------------------------------------------------------
--select count(*) from @EmployeeList
--select distinct emplid from @EmployeeList

Declare @EmployeeListLevelList as table(
EmplID varchar(255),
EmpNTID varchar(255),
[name] varchar(255),
RMID varchar(255),
RMNTID varchar(255),
TopManagerEmplID varchar(255),
TopManagerEmpNTID varchar(255),
[Level] int
);

Insert into @EmployeeListLevelList(EmplID,EmpNTID,[name],RMID,TopManagerEmplID,[Level])
select EmplID,EmpNTID,[name],RMID,TopManagerEmplID,'1'
from @EmployeeList where RMID is null

insert into @EmployeeListLevelList(EmplID,EmpNTID,[name],RMID,TopManagerEmplID,[Level])
select EmplID,EmpNTID,[name],RMID,TopManagerEmplID,'2'
from @EmployeeList where RMID = TopManagerEmplID

insert into @EmployeeListLevelList(EmplID,EmpNTID,[name],RMID,TopManagerEmplID,[Level])
select EmplID,EmpNTID,[name],RMID,TopManagerEmplID,'3'
from @EmployeeList where RMID in (select EmplID from @EmployeeListLevelList where [Level] = '2')

insert into @EmployeeListLevelList(EmplID,EmpNTID,[name],RMID,TopManagerEmplID,[Level])
select EmplID,EmpNTID,[name],RMID,TopManagerEmplID,'4'
from @EmployeeList where RMID in (select EmplID from @EmployeeListLevelList where [Level] = '3')

insert into @EmployeeListLevelList(EmplID,EmpNTID,[name],RMID,TopManagerEmplID,[Level])
select EmplID,EmpNTID,[name],RMID,TopManagerEmplID,'5'
from @EmployeeList where RMID in (select EmplID from @EmployeeListLevelList where [Level] = '4')

insert into @EmployeeListLevelList(EmplID,EmpNTID,[name],RMID,TopManagerEmplID,[Level])
select EmplID,EmpNTID,[name],RMID,TopManagerEmplID,'6'
from @EmployeeList where RMID in (select EmplID from @EmployeeListLevelList where [Level] = '5')

insert into @EmployeeListLevelList(EmplID,EmpNTID,[name],RMID,TopManagerEmplID,[Level])
select EmplID,EmpNTID,[name],RMID,TopManagerEmplID,'7'
from @EmployeeList where RMID in (select EmplID from @EmployeeListLevelList where [Level] = '6')

insert into @EmployeeListLevelList(EmplID,EmpNTID,[name],RMID,TopManagerEmplID,[Level])
select EmplID,EmpNTID,[name],RMID,TopManagerEmplID,'8'
from @EmployeeList where RMID in (select EmplID from @EmployeeListLevelList where [Level] = '7')

insert into @EmployeeListLevelList(EmplID,EmpNTID,[name],RMID,TopManagerEmplID,[Level])
select EmplID,EmpNTID,[name],RMID,TopManagerEmplID,'9'
from @EmployeeList where RMID in (select EmplID from @EmployeeListLevelList where [Level] = '8')

insert into @EmployeeListLevelList(EmplID,EmpNTID,[name],RMID,TopManagerEmplID,[Level])
select EmplID,EmpNTID,[name],RMID,TopManagerEmplID,'10'
from @EmployeeList where RMID in (select EmplID from @EmployeeListLevelList where [Level] = '9')

insert into @EmployeeListLevelList(EmplID,EmpNTID,[name],RMID,TopManagerEmplID,[Level])
select EmplID,EmpNTID,[name],RMID,TopManagerEmplID,'11'
from @EmployeeList where RMID in (select EmplID from @EmployeeListLevelList where [Level] = '10')

insert into @EmployeeListLevelList(EmplID,EmpNTID,[name],RMID,TopManagerEmplID,[Level])
select EmplID,EmpNTID,[name],RMID,TopManagerEmplID,'12'
from @EmployeeList where RMID in (select EmplID from @EmployeeListLevelList where [Level] = '11')

insert into @EmployeeListLevelList(EmplID,EmpNTID,[name],RMID,TopManagerEmplID,[Level])
select EmplID,EmpNTID,[name],RMID,TopManagerEmplID,'13'
from @EmployeeList where RMID in (select EmplID from @EmployeeListLevelList where [Level] = '12')

update a
set RMNTID = b.EMPNTID
from @EmployeeListLevelList a, @EmployeeListLevelList b
where a.RMID = b.EMPLID

update a
set TopManagerEmpNTID = b.EMPNTID
from @EmployeeListLevelList a, @EmployeeListLevelList b
where a.TopManagerEmplID = b.EMPLID

truncate table DAILY_EmployeeListLevelList_BADGED
insert into DAILY_EmployeeListLevelList_BADGED ([EmplID], [EmpNTID], [name], [RMID], [RMNTID], [TopManagerEmplID], [TopManagerEmpNTID], [Level])
select Distinct [EmplID], [EmpNTID], [name], [RMID], [RMNTID], [TopManagerEmplID], [TopManagerEmpNTID], [Level] from @EmployeeListLevelList
where EmplID is not null

truncate table DAILY_EmployeeListLevelList_Combined

insert into DAILY_EmployeeListLevelList_Combined (
[EmplID], [EmpNTID], [name], [MMID], [MMNTID], [MMTopManagerEmplID], [MMTopManagerEmpNTID], [MMLevel],
[RMID], [RMNTID], [RMTopManagerEmplID], [RMTopManagerEmpNTID], [RMLevel]
)

SELECT [EmplID], [EmpNTID], [name], [RMID], [RMNTID], [TopManagerEmplID], [TopManagerEmpNTID], [Level],
[RMID], [RMNTID], [TopManagerEmplID], [TopManagerEmpNTID], [Level]
FROM DAILY_EmployeeListLevelList_BADGED

END


We've moved on from SSRS to using Angular/Highcharts for reporting. I am having a difficult time modifying my original query to return something that allows me to create a report in Angular that allows a user to drill down on an employee and see the people who report to them:

+ Carmen
+ Annette
+ Eric
Alan
Courtney
Robert
Michael
+ Shireen
Alton
John
+ Jeffrey
Lynn
Abhishek


How can I structure my employee data to provide a report in Angular/Highcharts that allows me to expand a user to see the employees below them?

Continue reading...