August 20, 2012 at 5:07 am
Hi,
I have following table structure,
PositionIdReportToIdEmpIdIsVacantLevelNo
1012300
2112401
3112501
4212602
52Null12
6212802
7512903
8513003
From this I am able to calculate position count meaning number of positions under one position.
I have done this using CTE as below,
;WITH PCCET AS (
SELECT RootID = PosId, PosId
FROM Position
UNION ALL
SELECT cte.RootID, d.PosId
FROM PCCET cte
INNER JOIN Position d ON d.ReportTo = cte.PosId
)
select P.PosId, cnt.Children as PCnt
from Position P
INNER JOIN (
SELECT posid = RootID, Children = COUNT(*) - 1
FROM PCCET
GROUP BY RootID
) cnt ON cnt.posid = p.PosId
Now I wanted to calculate head count of each position meaning number of positions excluding vacant under each position.
So I want to get the following result;
PositionIdheadcount
16
22
30
40
52
60
70
80
August 21, 2012 at 4:00 am
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
August 21, 2012 at 5:12 am
Thanks a Lot,
This absolutely working fine for me. :)))
I was actually checking that Employee ID not null condition in CTE which was not calculating vacant positions and positions below vacant.
Thank you so much once again 🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply