SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Position count excluding Vacant positions in hierarchical data


Position count excluding Vacant positions in hierarchical data

Author
Message
vrushali.sawant
vrushali.sawant
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
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




laurie-789651
laurie-789651
UDP Broadcaster
UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)

Group: General Forum Members
Points: 1438 Visits: 1272
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
vrushali.sawant
vrushali.sawant
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 14
Thanks a Lot,

This absolutely working fine for me. Smile))

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 Smile
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search