[T-SQL] parent Child Recursive count and distribution in sql server.

---

parent Child Recursive count and distribution in sql server. Posted: 08 Aug 2013 11:21 PM PDT [b]I am having following table and data :[/b]CREATE TABLE dbo.Emp ( EmpID int PRIMARY KEY, EmpName varchar(30), MgrID int FOREIGN KEY REFERENCES Emp(EmpID) ) CREATE NONCLUSTERED INDEX NC_NU_Emp_MgrID ON dbo.Emp(MgrID) ; INSERT dbo.Emp SELECT 1, 'President', NULL INSERT dbo.Emp SELECT 2, 'Vice President', 1 INSERT dbo.Emp SELECT 3, 'CEO', 2 INSERT dbo.Emp SELECT 4, 'CTO', 2 INSERT dbo.Emp SELECT 5, 'Group Project Manager', 4 INSERT dbo.Emp SELECT 6, 'Project Manager 1', 5 INSERT dbo.Emp SELECT 7, 'Project Manager 2', 5 INSERT dbo.Emp SELECT 8, 'Team Leader 1', 6 INSERT dbo.Emp SELECT 9, 'Software Engineer 1', 8 INSERT dbo.Emp SELECT 10, 'Software Engineer 2', 8 INSERT dbo.Emp SELECT 11, 'Test Lead 1', 6 INSERT dbo.Emp SELECT 12, 'Tester 1', 11 INSERT dbo.Emp SELECT 13, 'Tester 2', 11 INSERT dbo.Emp SELECT 14, 'Team Leader 2', 7 INSERT dbo.Emp SELECT 15, 'Software Engineer 3', 14 INSERT dbo.Emp SELECT 16, 'Software Engineer 4', 14 INSERT dbo.Emp SELECT 17, 'Test Lead 2', 7 INSERT dbo.Emp SELECT 18, 'Tester 3', 17 INSERT dbo.Emp SELECT 19, 'Tester 4', 17 INSERT dbo.Emp SELECT 20, 'Tester 5', 17 -------------------------------------------------------------------With following sql i get the immediate child count for each parent.WITH ReportingTree (EmpID, EmpName, MgrID, Lvl) AS ( SELECT *, 0 as Lvl FROM emp WHERE MgrId IS NULL UNION ALL SELECT emp.EmpID, emp.EmpName, emp.MgrId, ReportingTree.Lvl+1 FROM emp INNER JOIN ReportingTree on emp.MgrID = ReportingTree.EmpID ) SELECT T.EmpName, count(T.EmpId) FROM ReportingTree T JOIN emp P ON P.MgrId = T.EmpID group by T.MgrId ,T.EmpName ,T.EmpId;Now i want to distribute the tasks equally among the childs...For e.g.500 tasks are allocated to President...now President has only one child i.e vice President...so all 500 tasks will be allocated to him.now vice president has two childs i.e CEO and CTO...so 250 tasks for CTO and 250 tasks for CEO will be allocated...and so on..i.e tasks will be distributed depending on the number of childs...So Question are?1 . where to store the primary tasks allocation (can we store it in emp table for each most parent )?2.can we do it in single sql or in procedure .?Thanks!

You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / T-SQL (SS2K8) To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610