Hi CP Members,
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 Maceij 3
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
This is What I Tried.
with cte (advId,ReferId,Level)
as
(
select AdvId,ReferId,1 as Level from table where ReferId=40
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
Thanks,