Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Position count excluding Vacant positions in hierarchical data Expand / Collapse
Author
Message
Posted Monday, August 20, 2012 5:07 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 12, 2013 12:17 AM
Points: 28, Visits: 14
Hi,

I have following table structure,



PositionId ReportToId EmpId IsVacant LevelNo
1 0 123 0 0
2 1 124 0 1
3 1 125 0 1
4 2 126 0 2
5 2 Null 1 2
6 2 128 0 2
7 5 129 0 3
8 5 130 0 3



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;



PositionId headcount
1 6
2 2
3 0
4 0
5 2
6 0
7 0
8 0



Post #1347081
Posted Tuesday, August 21, 2012 4:00 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, June 27, 2014 8:03 AM
Points: 320, Visits: 1,079
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...
Post #1347665
Posted Tuesday, August 21, 2012 5:12 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 12, 2013 12:17 AM
Points: 28, Visits: 14
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 :)
Post #1347710
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse