parent Child Recursive count and distribution in sql server.

Question

parent Child Recursive count and distribution in sql server.

akshay9986

SSC Veteran

Points: 202
More actions
August 9, 2013 at 6:21 am

#276741

I am having following table and data :
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!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286589 More actions · Answer 1

Hi and welcome to the forums. Great job posting ddl for the emp table. You lost me when you started talking about tasks. There is no table here and I don't understand where the 500 tasks come from or what you are trying to do.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

akshay9986 SSC Veteran Points: 202 More actions · Answer 2

thanks Sean Lange,

The 500 tasks are will be allocated to most top parents...

i m thinking that this 500 tasks can be stored in emp table itself...

These tasks will get distributed equaly among their childs..

so i need a sql or any other alternative...

Sean Lange SSC Guru Points: 286589 More actions · Answer 3

akshay9986 (8/9/2013)
thanks Sean Lange,
The 500 tasks are will be allocated to most top parents...
i m thinking that this 500 tasks can be stored in emp table itself...
These tasks will get distributed equaly among their childs..
so i need a sql or any other alternative...

I would recommend not putting your tasks the emp table. That would be denormalized and maintenance would quickly become a nightmare. I would instead create a tasks table. Then if any given task can have only 1 emp assigned to it, add a empID column to the task table. If a task can have more than 1 emp assigned to it you would create an intermediate table that holds empID and TaskID only.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Jeff Moden SSC Guru Points: 1003913 More actions · Answer 4

akshay9986 (8/9/2013)
thanks Sean Lange,
The 500 tasks are will be allocated to most top parents...
i m thinking that this 500 tasks can be stored in emp table itself...
These tasks will get distributed equaly among their childs..
so i need a sql or any other alternative...

What do you want done if the branches in the downline aren't equal in length at the leaf level?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 5

akshay9986 (8/9/2013)
The 500 tasks are will be allocated to most top parents...
i m thinking that this 500 tasks can be stored in emp table itself...

That depends on the relation between employee and tasks. If an employee can have more than one task - and that sounds likely - you will need a new table. If an employee can only have one task at a time, that begs the question of what happens when there are more tasks than employees at the leaf level.

There is also the question whether you should store the tasks on the intermediate levels as well (in which case you must have a sub-table, or only the leaf levels).

As for actually distributing the tasks, let's get the data model straight first. And we would need to know how the tasks are stored. What about if some employees already have tasks allocated when we start working?

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]