(query) Group and distributes data evenly based on duration of tasks to be completed

[Dan D]

Here is an example of a table that keeps a list of tasks and the time it
takes to complete them (in minutes)


TaskID TaskName Duration_Minutes
------- ----------- ------------------

1 Task A 100
2 Task A1 20
3 Task A2 40
4 Task A3 80
5 Task A4 20
6 Task A5 35
7 Task A6 5
8 Task A7 5
9 Task A8 5
10 Task A9 5
11 Task B 5
12 Task B1 10
13 Task B2 20
14 Task B3 20
15 Task B4 20
16 Task B5 60


I also have a table of Personnel. This is the table that lets you know who
the ones are that are going to be performing the tasks.

EmpID EmpName
------ -----------
1 Employee_A
2 Employee_B
3 Employee_C


So, in this example there is 450 minutes of work that needs to be performed
and has to be split up evenly between the 3 employees.

So I would like to write a query that will split the work up as evenly as
possible. The maximum amount of work to any employee is 200 minutes.

An example of the output would be:

Employee_A
-------------
1 Task A 100
2 Task A1 20
12 Task B1 10
13 Task B2 20


Employee_B
-------------
3 Task A2 40
4 Task A3 80
5 Task A4 20
10 Task A9 5
11 Task B 5

Employee_C
-------------
6 Task A5 35
7 Task A6 5
8 Task A7 5
9 Task A8 5
14 Task B3 20
15 Task B4 20
16 Task B5 60

 

[Roji. P. Thomas]

I cannot think of a set based solution.
If some sort of loop or cursor is ok for you then you can do something like

1. Take each task
2. Find the Employee with the lowest amount of task and assign the current
task to him.
3. Go back to step 1

If you need further help in converting it to T-SQL, post back.

 

[Tom Wickerath]

Why not use Microsoft Project instead? I'm not enough of a Project expert to know if it can
split up tasks evenly, although I think it has a feature called something like "leveling". In
any case, Project is written specifically to deal with project tasks, including durations and
task dependencies.

Tom
___________________________________


Here is an example of a table that keeps a list of tasks and the time it
takes to complete them (in minutes)


TaskID TaskName Duration_Minutes
------- ----------- ------------------

1 Task A 100
2 Task A1 20
3 Task A2 40
4 Task A3 80
5 Task A4 20
6 Task A5 35
7 Task A6 5
8 Task A7 5
9 Task A8 5
10 Task A9 5
11 Task B 5
12 Task B1 10
13 Task B2 20
14 Task B3 20
15 Task B4 20
16 Task B5 60


I also have a table of Personnel. This is the table that lets you know who
the ones are that are going to be performing the tasks.

EmpID EmpName
------ -----------
1 Employee_A
2 Employee_B
3 Employee_C


So, in this example there is 450 minutes of work that needs to be performed
and has to be split up evenly between the 3 employees.

So I would like to write a query that will split the work up as evenly as
possible. The maximum amount of work to any employee is 200 minutes.

An example of the output would be:

Employee_A
-------------
1 Task A 100
2 Task A1 20
12 Task B1 10
13 Task B2 20


Employee_B
-------------
3 Task A2 40
4 Task A3 80
5 Task A4 20
10 Task A9 5
11 Task B 5

Employee_C
-------------
6 Task A5 35
7 Task A6 5
8 Task A7 5
9 Task A8 5
14 Task B3 20
15 Task B4 20
16 Task B5 60