sql - How do I use CTE for this -

sql - How do I use CTE for this -

this sample table structure,

create table #table(advid int identity(1,1),name nvarchar(100),ranks nvarchar(5),referid int ,referalrank nvarchar(5)) insert #table(name,ranks,referid,referalrank) values('king','mgr',0,'0') insert #table (name,ranks,referid,referalrank) values('maceij','mgr',1,'mgr') insert #table (name,ranks,referid,referalrank) values('los','mgr',1,'mgr') insert #table (name,ranks,referid,referalrank) values('los1','adv',1,'mgr') insert #table (name,ranks,referid,referalrank) values('griff','mgr',1,'mgr') insert #table (name,ranks,referid,referalrank) values('sa','mgr',2,'mgr') insert #table (name,ranks,referid,referalrank) values('cassandra','mgr',2,'mgr') insert #table (name,ranks,referid,referalrank) values('jason','mgr',3,'mgr') insert #table (name,ranks,referid,referalrank) values('smith','mgr',3,'mgr') insert #table (name,ranks,referid,referalrank) values('akee','mgr',6,'mgr') insert #table (name,ranks,referid,referalrank) values('manasa','adv',6,'mgr') insert #table (name,ranks,referid,referalrank) values('akee','mgr',10,'mgr') insert #table (name,ranks,referid,referalrank) values('manasa','adv',10,'mgr') select *from #table

let me have words table construction here , advid 1 referred admin , (2,3,4,5) 1st level of 1 (6,7,8,9) 2nd level of 1 (10,11) 3rd level of 1 (12,13) 4 th level of 1 same logic each advisors like structure

how select count of manager(how many manager under agent) each advisors 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 tried.

with cte (advid,referid,level) ( select advid,referid,1 level table referid=1 union select a.advid,a.referid ,level+1 table inner bring together cte b on b.advid=a.referid ) select count(b.advid) cte inner bring together table b on a.advid=b.advid a.level<=3 , b.ranks='mgr'

i hope clear ,assist me result

my result bit different yours in first 2 rows, because king , maceij missing 12 (akee), if have 12 10, must have 1 , 2 advids.

first find deep level of every row.

second set parents each of rows.

third count childs

and lastly show result.

;with alignref ( select advid, name, ranks, referid, referalrank, 0 deeplevel #table referid = 0 union select t.advid, t.name, t.ranks, t.referid, t.referalrank, r.deeplevel+1 deeplevel #table t inner bring together alignref r on t.referid = r.advid --and t.ranks = r.ranks ), getparents ( select advid, name, ranks, referid, referalrank, deeplevel, advid parentid alignref ranks='mgr' , deeplevel <= 4 union select r.advid, r.name, r.ranks, r.referid, r.referalrank, r.deeplevel, cc.parentid parentid getparents cc inner bring together alignref r on cc.advid = r.referid r.ranks='mgr' , r.deeplevel <= 4 ), countchilds ( select parentid, count(*) amount getparents advid <> parentid grouping parentid ) select t.advid, t.name, isnull(cc.amount, 0) countofmanager #table t left bring together countchilds cc on cc.parentid = t.advid order 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

sql sql-server