I think this will do it:

-- Data (corrected column names):

if object_id('tempdb..#Position') is not null drop table #Position;

create table #Position

(

PosId Int,

ReportTo Int,

EmpId Int,

IsVacant Int,

LevelNo Int

);

insert into #Position values (1, 0, 123, 0, 0);

insert into #Position values (2, 1, 124, 0, 1);

insert into #Position values (3, 1, 125, 0, 1);

insert into #Position values (4, 2, 126, 0, 2);

insert into #Position values (5, 2, Null, 1, 2);

insert into #Position values (6, 2, 128, 0, 2);

insert into #Position values (7, 5, 129, 0, 3);

insert into #Position values (8, 5, 130, 0, 3);

select * from #Position;

-- Query:

;WITH PCCET AS

(

SELECT RootID = PosId,

PosId, EmpId

FROM #Position

UNION ALL

SELECT cte.RootID, d.PosId, d.EmpId

FROM PCCET cte

INNER JOIN #Position d ON d.ReportTo = cte.PosId

)

--select * from PCCET order by rootid

select P.PosId,

ISNULL(cnt.Children, 0) as PCnt

from #Position P

LEFT OUTER JOIN (

SELECT posid = RootID, Children = COUNT(*)

FROM PCCET

WHERE EmpId is not null

AND RootId <> PosId

GROUP BY RootID

) cnt ON cnt.posid = p.PosId

My results are different to yours - 2 has 4 occupied positions: 4, 6, 7 & 8 with 5 unoccupied.

I added the EmpId into the CTE, then ignored records where EmpId is NULL.

I excluded the manager by ignoring RootId = PosId instead of subtracting 1 from the count. This avoids the problem of the unoccupied manager job 5.

The LEFT JOIN instead of inner join keeps all the managers in the output.

By the way - if you provide proper data definitions you will be more likely to get help, as many people don't have the time to write it themselves...:-D