Reputation: 1213
This is my Sample table Structure,
Create table #table(advId int identity(1,1),name nvarchar(100),ranks nvarchar(5),ReferId int ,ReferalRank nvarchar(5))
insert into #table(name,ranks,ReferId,ReferalRank) values('King','MGR',0,'0')
insert into #table (name,ranks,ReferId,ReferalRank) values('Maceij','MGR',1,'MGR')
insert into #table (name,ranks,ReferId,ReferalRank) values('Los','MGR',1,'MGR')
insert into #table (name,ranks,ReferId,ReferalRank) values('Los1','ADV',1,'MGR')
insert into #table (name,ranks,ReferId,ReferalRank) values('Griff','MGR',1,'MGR')
insert into #table (name,ranks,ReferId,ReferalRank) values('SA','MGR',2,'MGR')
insert into #table (name,ranks,ReferId,ReferalRank) values('CASSANDRA','MGR',2,'MGR')
insert into #table (name,ranks,ReferId,ReferalRank) values('Jason','MGR',3,'MGR')
insert into #table (name,ranks,ReferId,ReferalRank) values('Smith','MGR',3,'MGR')
insert into #table (name,ranks,ReferId,ReferalRank) values('Akee','MGR',6,'MGR')
insert into #table (name,ranks,ReferId,ReferalRank) values('Manasa','ADV',6,'MGR')
insert into #table (name,ranks,ReferId,ReferalRank) values('Akee','MGR',10,'MGR')
insert into #table (name,ranks,ReferId,ReferalRank) values('Manasa','ADV',10,'MGR')
select *from #table
Let me have words about my Table Structure Here , AdvId 1 is Referred by admin , (2,3,4,5) are 1st level of 1 (6,7,8,9) are 2nd Level of 1 (10,11) are 3rd level of 1 (12,13) are 4 th Level of 1 same Logic For Each Advisors Like This Structure
How do I Select the Count Of Manager(how many manager under the agent) for Each Advisors up to 3 Levels
advId name CountOfmanager
1 king 8 --2,3,5,6,7,8,9,10
2 Maceij 3 --6,7,10
3 los 2 --8,9
4 Los1 0 -- nobody
5 Griff 0 -- nobody
6 SA 2 -- 10,12
7 CASSANDRA 0 -- nobody
8 Jason 0
9 Smith 0
10 Akee 1 --12
11 manasa 0
12 Akee 0
13 Manasa 0
This is what I Tried.
with cte (advId,ReferId,Level)
as
(
select AdvId,ReferId,1 as Level from table where ReferId=1
union all
select a.AdvId,a.ReferId ,Level+1 from table
as a inner join cte as b on b.AdvId=a.ReferId
)
select COUNT(b.AdvId) From cte as a inner join table
as b on a.advId=b.advId where a.level<=3 and b.ranks='MGR'
I hope its Clear ,Assist me to get the Result
Upvotes: 0
Views: 128
Reputation: 2768
My result a bit different from yours in first 2 rows, because for king and Maceij you missing 12 (Akee), if you have 12 for 10, you also must have it for 1 and 2 advIDs.
First I find deep level of every row.
Second I put parents to each of rows.
Third count childs
And for the last I just show result.
;WITH alignRef AS (
SELECT advId, name, ranks, ReferId, ReferalRank, 0 AS DeepLevel
FROM #table
WHERE ReferId = 0
UNION ALL
SELECT T.advId, T.name, T.ranks, T.ReferId, T.ReferalRank, R.DeepLevel+1 AS DeepLevel
FROM #table T
INNER JOIN alignRef AS R
ON T.ReferId = R.advId
--AND T.ranks = R.ranks
), getParents AS (
SELECT advId, name, ranks, ReferId, ReferalRank, DeepLevel, advId AS ParentID
FROM alignRef
WHERE ranks='MGR' AND DeepLevel <= 4
UNION ALL
SELECT R.advId, R.name, R.ranks, R.ReferId, R.ReferalRank, R.DeepLevel, CC.ParentID AS ParentID
FROM getParents AS CC
INNER JOIN alignRef AS R
ON CC.advId = R.ReferId
WHERE R.ranks='MGR' AND R.DeepLevel <= 4
), CountChilds AS (
SELECT ParentID, COUNT(*) AS Amount
FROM getParents
WHERE advId <> ParentID
GROUP BY ParentID
)
SELECT T.advId, T.name, ISNULL(CC.Amount, 0) AS CountOfmanager
FROM #table AS T
LEFT JOIN CountChilds AS CC
ON CC.ParentID = T.advId
ORDER BY T.advId
Result:
advId name CountOfmanager
1 King 9
2 Maceij 4
3 Los 2
4 Los1 0
5 Griff 0
6 SA 2
7 CASSANDRA 0
8 Jason 0
9 Smith 0
10 Akee 1
11 Manasa 0
12 Akee 0
13 Manasa 0
Upvotes: 2