Grouping in recursive function?
-
I have a self referencing table just like Employees table in Northwind. What I want is for a particular CategoryID it should get all its immediate children and count all the items belonging to that sub category and its sub categories (there is no limit for the level of nesting, but it is not more than 32).
Presently I am using a recursive function to get all the children of a particular Id, but is not able to group it. I can post the code if needed.
Thanks in advance.
-
Sounds like a hierarchy navigation problem so I would adapt one of the hierarchy user defined functions to return the count you want given the input ID.
-
how about using temporary objects to explode your relationships and then count the temporary objects (@table, #table or ##table).
declare @tmpRelations table (HiarchyLevel integer not null, EmployeeID integer not null, ReportsTo integer not null)
Declare @CurrentLevel integer
set @CurrentLevel = 1
Insert into @tmpRelations
SELECT @CurrentLevel, EmployeeID, ReportsTo
FROM Northwind.dbo.Employees
where Reportsto = @TheBoss
while @@rowcount > 0
begin
set @CurrentLevel = @CurrentLevel + 1
Insert into @tmpRelations
SELECT @CurrentLevel, E.EmployeeID, E.ReportsTo
FROM Northwind.dbo.Employees E
inner join @tmpRelations R
on E.ReportsTo = R.EmployeeID
and R.HiarchyLevel = @CurrentLevel - 1
end
Select count(*) as Number_Of_Deciples
from @tmpRelations
Select *
from @tmpRelations
where HiarchyLevel = 1 --Direct dependant members
You'll get the gripp
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply