Help required for CTE
-
This is my stored procedure ALTER PROCEDURE TeamStructure ( @rootid int ) AS WITH MyTeam AS ( SELECT *, [Name] AS ParentName, 0 AS TeamLevel FROM tbl_team WHERE ParentID =@rootid UNION ALL SELECT Fam.*,MyTeam.Name AS ParentName, TeamLevel + 1 FROM tbl_team AS Fam INNER JOIN MyTeam ON Fam.ParentID = MyTeam.ID) SELECT * FROM MyTeam It will return the complete hierarchical records. Level 0 will have root member Level 1 will have 2 members Level 2 willhave 4 members Level 3 will have 8 members or if level =n members= 2^n I need to perform the following operation: for any level if members are less than 2^n the iteration should stop and return the number of members on that level and the number of level For example if the number of members on level 4 are 12 The procedure should stop and return level :4 Members :12 but if the number of members on nth level is exactly 2^n the iteration must look for the next level Can anyone modify this procedure and help me please? TIA