Click here to Skip to main content
14,870,616 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
SELECT COUNT(*) FROM [dbo].[tblEmployeeMaster] WHERE MGR_ID=@EMPID


table tblEmployeeMaster is having key columns emp_no,emp_name,mgr_id.

Here I want fetch count of a person's reportees, child reportees(nth level)
that means his total team count

What I have tried:

Ex: Manager JOHN has 10 direct reportees, these 10 people also managers.

now need to get JOHN's total team count
Posted
Updated 11-Mar-21 0:03am

Would give you a fully worked example but I'm just about to go into a meeting.

I did something similar in my article about loops in SQL Server - see Processing Loops in SQL Server[^] the section about "Traversing a Hierarchy".

You can then filter (or count!) the results based on the MtoE calculated column

EDIT after OP comment:

Here is a worked example using your column and table names (but note I have used a table variable so remove the "@"). I set up this sample data
SQL
declare @tblEmployeeMaster table (emp_no int, emp_name nvarchar(50), mgr_id int)
insert into @tblEmployeeMaster (emp_no, emp_name, mgr_id) values
(1, 'John', NULL),
-- John's direct reports
(2, 'Direct Report 1', 1),(3, 'Direct Report 2', 1),(4, 'Direct Report 3', 1),(5, 'Direct Report 4', 1),(6, 'Direct Report 5', 1),
(7, 'Direct Report 6', 1),(8, 'Direct Report 7', 1),(9, 'Direct Report 8', 1),(10, 'Direct Report 9', 1),(11, 'Direct Report 10', 1),
-- Report to Direct Report 1
(12, 'Reports to DR1#1',2),(13, 'Reports to DR1#2',2),
-- Report to Direct Report 2
(14, 'Reports to DR2#1',3),
-- Report to Direct Report 3
(15, 'Reports to DR3#1',4),(16, 'Reports to DR3#2',4),(17, 'Reports to DR3#3',4)
-- etc etc 
So John has 16 reportees down the hierarchy.
I set up a variable to capture his emp_no surrounded by "/" but this could be the parameter to a user function. Note the type is varchar not int
SQL
DECLARE @IDtoCheck varchar(5) = (SELECT '/' + CAST(emp_no as varchar(5)) + '/' FROM @tblEmployeeMaster WHERE [emp_name] = 'John')
Then I tweaked the CTE from the article as follows
SQL
;WITH Emp_CTE AS
(
    SELECT emp_no, mgr_id, emp_name
        , 1 as RLevel
        , MtoE = CAST(isnull(mgr_id,0) AS VARCHAR(MAX)) + '/' +
            CAST(emp_no AS VARCHAR(MAX))
    FROM @tblEmployeeMaster
    WHERE mgr_id IS NULL
    UNION ALL
    SELECT e.emp_no, e.mgr_id, e.emp_name
        , RLevel + 1
        , MtoE = MtoE + '/' + CAST(e.emp_no AS VARCHAR(MAX))
    FROM @tblEmployeeMaster e
    INNER JOIN Emp_CTE ecte ON ecte.emp_no = e.mgr_id
)
SELECT COUNT(*) as TotalReportees
FROM Emp_CTE
where CHARINDEX('/' + @IDtoCheck + '/', MtoE ) > 0
   
v3
Comments
   
@CHill60 it's a great article, I have used the CTE for generating the org hierarchy.
but not understanding how to implement for this scenario.
Thanks in advance for your answer.
CHill60 11-Mar-21 5:22am
   
I've updated my solution
   
Thank you for your detailed solution. I have also achieved the same using CTE.
SQL
WITH CTEORG AS (
    SELECT Emp_No, Emp_Name,MGR_ID,DOL,LEAVING_ACTION_DATE
	FROM [tblEmployeeMasterLive]
    WHERE MGR_ID =@EMP_NO AND CONVERT(DATETIME,DOJ,103) <= @STARTDATE AND (CONVERT(DATETIME,DOL,103) IS NULL OR CONVERT(DATETIME,DOL,103) >= @STARTDATE)
UNION ALL
    SELECT e.Emp_No, e.Emp_Name,e.MGR_ID,e.DOL,e.LEAVING_ACTION_DATE
    FROM [tblEmployeeMasterLive] e
    INNER JOIN CTEORG o 
    ON o.Emp_No = e.MGR_ID
)
SELECT COUNT(*) FROM CTEORG;
   
Comments
CHill60 11-Mar-21 8:45am
   
Why do you convert DOL and DOJ? They should be Date or Datetime types as should @STARTDATE. If your version of SQL server does not support the Date type you may find this link useful Some Common Date Routines – SQLServerCentral[^]
   
Source data comes from oracle database, hence converting
CHill60 12-Mar-21 3:29am
   
Oracle databases support datetime. My comments still apply.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900