Home » SQL & PL/SQL » SQL & PL/SQL » SQL query for a project scheduler (11.2.0.3)
SQL query for a project scheduler [message #631100] |
Fri, 09 January 2015 03:50 |
Amine
Messages: 371 Registered: March 2010
|
Senior Member |
|
|
Hi all,
We have a set of projects to achieve. Projects are done by members and projects are scheduled on weeks.
Some times, members are not available for a week.
Members that are available on a week and have no project to do are considered as maintainers. They work then on a sort of help desk.
So here is the schema and the data :
drop table members;
create table members
(
id_mmb int primary key ,
nom varchar2(100)
)
/
insert into members values (1, 'MEMBER1');
insert into members values (2, 'MEMBER2');
insert into members values (3, 'MEMBER3');
insert into members values (4, 'MEMBER4');
insert into members values (5, 'MEMBER5');
insert into members values (6, 'MEMBER6');
insert into members values (7, 'MEMBER7');
insert into members values (8, 'MEMBER8');
insert into members values (9, 'MEMBER9');
insert into members values (10, 'MEMBER10');
--==========================================
drop table projects;
create table projects
(
id_proj int primary key ,
lib_proj varchar2(100) ,
priority int ,
nb_weeks int
)
/
insert into projects(id_proj, lib_proj, priority, nb_weeks) values (1, 'PROJ1' , 1, 12);
insert into projects(id_proj, lib_proj, priority, nb_weeks) values (2, 'PROJ2' , 2, 5);
insert into projects(id_proj, lib_proj, priority, nb_weeks) values (3, 'PROJ3' , 3, 2);
insert into projects(id_proj, lib_proj, priority, nb_weeks) values (4, 'PROJ4' , 4, 10);
insert into projects(id_proj, lib_proj, priority, nb_weeks) values (5, 'PROJ5' , 5, 3);
insert into projects(id_proj, lib_proj, priority, nb_weeks) values (6, 'PROJ6' , 6, 3);
insert into projects(id_proj, lib_proj, priority, nb_weeks) values (7, 'PROJ7' , 7, 3);
insert into projects(id_proj, lib_proj, priority, nb_weeks) values (8, 'PROJ8' , 8, 2);
insert into projects(id_proj, lib_proj, priority, nb_weeks) values (9, 'PROJ9' , 9, 2);
insert into projects(id_proj, lib_proj, priority, nb_weeks) values (10, 'PROJ10' , 10, 3);
insert into projects(id_proj, lib_proj, priority, nb_weeks) values (11, 'PROJ11' , 3, 2);
--==========================================
drop table project_mmb;
create table project_mmb
(
id_mmb int ,
id_proj int
)
/
insert into project_mmb values (2,11);
insert into project_mmb values (4,11);
insert into project_mmb values (3,1);
insert into project_mmb values (5,1);
insert into project_mmb values (6,1);
insert into project_mmb values (10,1);
insert into project_mmb values (8,2);
insert into project_mmb values (9,2);
insert into project_mmb values (10,2);
insert into project_mmb values (4,3);
insert into project_mmb values (5,3);
insert into project_mmb values (10,3);
insert into project_mmb values (2,4);
insert into project_mmb values (4,4);
insert into project_mmb values (9,4);
insert into project_mmb values (2,8);
insert into project_mmb values (4,8);
insert into project_mmb values (8,8);
insert into project_mmb values (1,5);
insert into project_mmb values (4,5);
insert into project_mmb values (5,5);
insert into project_mmb values (6,5);
insert into project_mmb values (9,5);
insert into project_mmb values (10,5);
insert into project_mmb values (4,6);
insert into project_mmb values (6,6);
insert into project_mmb values (8,6);
insert into project_mmb values (1,7);
insert into project_mmb values (6,7);
insert into project_mmb values (8,7);
insert into project_mmb values (9,7);
insert into project_mmb values (4,9);
insert into project_mmb values (6,9);
insert into project_mmb values (1,10);
insert into project_mmb values (3,10);
insert into project_mmb values (10,10);
--==========================================
drop table weeks;
create table weeks
(
id_week int primary key ,
dat_start date
)
/
insert into weeks
select level, to_date('04/01/2015', 'dd/mm/yyyy') + 7 * level
from dual
connect by level <= 30
;
--==========================================
drop table unavailable_mmb;
create table unavailable_mmb
(
id_mmb int ,
id_week int
)
/
insert into unavailable_mmb values (2,2);
insert into unavailable_mmb values (2,9);
insert into unavailable_mmb values (2,16);
insert into unavailable_mmb values (3,3);
insert into unavailable_mmb values (3,10);
insert into unavailable_mmb values (3,17);
insert into unavailable_mmb values (4,4);
insert into unavailable_mmb values (4,11);
insert into unavailable_mmb values (4,18);
insert into unavailable_mmb values (6,2);
insert into unavailable_mmb values (6,3);
insert into unavailable_mmb values (6,7);
insert into unavailable_mmb values (6,11);
insert into unavailable_mmb values (6,12);
insert into unavailable_mmb values (6,16);
insert into unavailable_mmb values (6,20);
insert into unavailable_mmb values (7,1);
insert into unavailable_mmb values (7,2);
insert into unavailable_mmb values (7,3);
insert into unavailable_mmb values (8,6);
insert into unavailable_mmb values (8,15);
insert into unavailable_mmb values (9,3);
insert into unavailable_mmb values (9,10);
insert into unavailable_mmb values (9,17);
insert into unavailable_mmb values (10,6);
insert into unavailable_mmb values (10,15);
--==========================================
Now I want to schedule projects according to their importance (priority).
Also, we don't want to serialize projects. Meaning we don't want to do the project with priority 1 then when it ends we start project with priority 2
and so on. We want to go in a parallel way : project with priority 1 then project with priority 2 then we go back to project with priority 1...in a cycle way.
Here is another requirements :
1. projects are scheduled when they had at least 2 available members.
2. 2 projects at most are scheduled in a week.
I started with this query :
select *
from
(
with available_mmb as
(
select id_week
, id_mmb
from weeks, members
where 1 = 1
minus
select weeks.id_week
, id_mmb
from weeks, unavailable_mmb
where 1 = 1
and weeks.id_week = unavailable_mmb.id_week
order by id_week, id_mmb
), available_mmb_with_proj as
(
select
id_week
, id_proj
, available_mmb.id_mmb
, count(*) over (partition by id_week, id_proj) nb_mmb
from available_mmb, project_mmb
where 1 = 1
and available_mmb.id_mmb = project_mmb.id_mmb(+)
), v_ranked_proj as
(
select
id_week
, available_mmb_with_proj.id_proj
, id_mmb
, nb_mmb
, priority
, dense_rank() over (partition by id_week order by nvl(priority, 9999) asc) rnk
from available_mmb_with_proj, projects
where 1 = 1
and available_mmb_with_proj.id_proj = projects.id_proj(+)
), v_all as
(
select
v_ranked_proj.id_week id_week
, dat_start
, id_proj
, id_mmb
, nb_mmb
, priority
, rnk
from v_ranked_proj, weeks
where 1 = 1
and v_ranked_proj.id_week = weeks.id_week
and v_ranked_proj.id_week <= 2
--and (nb_mmb >= 2 and rnk <= 2 or id_proj is null)
--and (id_proj in (3,4) or id_proj is null)
)
select
v_all.*
,
(
select
count(distinct id_mmb)
from available_mmb_with_proj
where id_week = v_all.id_week
and (id_week, id_mmb) not in (select id_week, id_mmb from v_all a where id_mmb = v_all.id_mmb)
)
nb_help_desk
from v_all
)
where 1 = 1
--and (rnk <= 2 or
order by id_week, id_proj
;
But I had some problems with it :
1. Projects are not consuming weeks : meaning with my query, projects never ends !
2. The help_desk members are not calculated properly. I find my query with this point clear, but it is not giving the right result.
3. I didn't find a way to alternate projects.
I've found here a SQL Sudoku solver here, why not a SQL project scheduler solver !
Appreciate any suggestion,
Thanks in advance,
Amine
|
|
|
|
|
|
Re: SQL query for a project scheduler [message #631158 is a reply to message #631156] |
Fri, 09 January 2015 14:50 |
Amine
Messages: 371 Registered: March 2010
|
Senior Member |
|
|
Thanks Mr Kevin Meade for your interest.
What is the start week of each project?
The purpose is to get the best schedule that allows us to finish all projects, with respect to their priority. So we do not have the start date of each project. The query is supposed to give it to us.
The query delivers a lot of solutions, and we are looking for the best one.
What is the start date of each member on each project?
When a project is scheduled on a week, it means that at most, all the people involved in the project can work on it. So the project is not devided into tasks so we can give them to members.
Can members work on more than one project at a time?
Yes they can.
Is it a requirement that you get just one query that gives the result or can you use a process to generate it?
Not necessarily. As I said in the first question, I suppose that there are a lot of solutions, and we have to give the best one that respects the constraints and that achieve the goals. "Lot of solutions" so I thought about a SQL query, but any other process is welcomed.
If not a process, then are you allowed to create for example a pl/sql procedure that does the work and exposes the result as a set of rows?
Answered it previously .
Thanks in advance,
Amine
|
|
|
|
|
|
|
|
|
|
Re: SQL query for a project scheduler [message #631253 is a reply to message #631250] |
Sun, 11 January 2015 15:27 |
|
Kevin Meade
Messages: 2103 Registered: December 1999 Location: Connecticut USA
|
Senior Member |
|
|
I have worked out one solution to your problem. However, doing this has revealed that there are still plenty of issues with your task. Most notably:
1. I equate a member to single person.
2. since there is no "avaiable" weeks per member, I assume that a member is available for all weeks unless they show up in unavailable.
3. I have also assumed that even though a member may be assigned to multiple projects and could in fact be scheduled for multiple projects in any given week, in order to get higher priority projects done ahead of lower priority projects, each member will be assignd to only one project per week in the actual schedule. Otherwise they become available to put part of their time to lower priority projects at the expense of higher priority projects which is contrary to getting the higher priority projects done first.
4. I have also assumed that nb_weeks is a limit to the number of weeks a project can be worked on and is thus an additional rule to check, and not some summary data element noting how many weeks the project has been scheduled for work which would be a desing error in your table designs.
5. ** most importantly, you have said that there are "Multiple" solutions to this problem. But this is not really true if my assumptions above are correct. It is that or I have missed something significant in the problem definitino.
So here is my solution for what it is worth. I suggest:
1. if you use it, test the crap out of it (I have done only very minimal testing), understand it, and make sure there are no missing rules that need to be met for which this solution does not account.
2. you really should use this to understand the approach I have used. That is, use of object types and table functions to provide a procedural solution to your problem.
Also, after looking at this, I realize that someone smarter than I might be able to produce a single query without any procedural code by using one or both of:
1. recursive hiearchical query
2. model clause
But I leave that for others to explore. Good luck, Kevin.
drop function get_schedule;
drop type c_member_project_week;
drop type o_member_project_week;
create or replace type o_member_project_week as object (id_mmb integer,id_proj integer,id_week integer)
/
create or replace type c_member_project_week as table of o_member_project_week
/
create or replace function get_schedule return c_member_project_week
as
c_member_project_week_gv c_member_project_week := c_member_project_week();
id_proj_gv integer;
project_count_v integer;
begin
for r1 in (
select id_week
from weeks
order by dat_start
) loop null;
begin
project_count_v := 0;
loop null;
--
-- get the next project we can assign members to for the target week
-- keep doing this till no new projects can be assigned to the target week
-- then move on to the next week
--
-- members must be assigned to work on the project
-- members must be avaliable to work during the target week
-- members must not already be working on some other project during the target week
-- projects must have at least two members available to work on them given the above
-- projects are limited to a maxium number of scheduled weeks of nb_weeks
-- projects are assigned in order of priority
--
-- only assign a maximum of two projects per week
--
select id_proj
into id_proj_gv
from (
select id_proj
from projects
where id_proj in (
select project_mmb.id_proj
from project_mmb
,(
select id_mmb
from members
where id_mmb not in (
select id_mmb
from table(cast(c_member_project_week_gv as c_member_project_week))
where id_week = r1.id_week
)
minus
select id_mmb
from unavailable_mmb
where id_week = r1.id_week
) avail_mmb
where project_mmb.id_mmb = avail_mmb.id_mmb
group by project_mmb.id_proj
having count(*) > 1
)
and not exists (
select 1 c1
from table(cast(c_member_project_week_gv as c_member_project_week)) x
where x.id_proj = projects.id_proj
group by x.id_week
having count(*) < projects.nb_weeks
)
order by priority
)
where rownum = 1
;
--
-- add a line to the schedule
--
select cast(multiset(
select * from (
select *
from table(cast(c_member_project_week_gv as c_member_project_week))
union all
select project_mmb.id_mmb,id_proj_gv,r1.id_week
from project_mmb
,(
select id_mmb
from members
where id_mmb not in (
select id_mmb
from table(cast(c_member_project_week_gv as c_member_project_week))
where id_week = r1.id_week
)
minus
select id_mmb
from unavailable_mmb
where id_week = r1.id_week
) avail_mmb
where project_mmb.id_mmb = avail_mmb.id_mmb
and id_proj = id_proj_gv
)
) as c_member_project_week
)
into c_member_project_week_gv
from dual
;
project_count_v := project_count_v + 1;
if project_count_v >= 2 then exit; end if;
end loop;
exception when no_data_found then null;
end;
end loop;
return (c_member_project_week_gv);
end;
/
show errors
select *
from table(cast(get_schedule as c_member_project_week))
order by id_week,id_proj,id_mmb
/
ID_MMB ID_PROJ ID_WEEK
---------- ---------- ----------
3 1 1
5 1 1
6 1 1
10 1 1
8 2 1
9 2 1
4 3 2
5 3 2
10 3 2
1 5 2
9 5 2
5 3 3
10 3 3
2 11 3
4 11 3
5 3 4
10 3 4
2 4 4
9 4 4
5 3 5
10 3 5
2 11 5
4 11 5
1 7 6
6 7 6
9 7 6
2 11 6
4 11 6
5 3 7
10 3 7
2 11 7
4 11 7
5 3 8
10 3 8
2 11 8
4 11 8
4 3 9
5 3 9
10 3 9
6 6 9
8 6 9
5 3 10
10 3 10
2 11 10
4 11 10
5 3 11
10 3 11
1 7 11
8 7 11
9 7 11
5 3 12
10 3 12
2 11 12
4 11 12
5 3 13
10 3 13
2 11 13
4 11 13
5 3 14
10 3 14
2 11 14
4 11 14
1 7 15
6 7 15
9 7 15
2 11 15
4 11 15
4 3 16
5 3 16
10 3 16
1 7 16
8 7 16
9 7 16
5 3 17
10 3 17
2 11 17
4 11 17
5 3 18
10 3 18
1 7 18
6 7 18
8 7 18
9 7 18
5 3 19
10 3 19
2 11 19
4 11 19
5 3 20
10 3 20
2 11 20
4 11 20
5 3 21
10 3 21
2 11 21
4 11 21
5 3 22
10 3 22
2 11 22
4 11 22
5 3 23
10 3 23
2 11 23
4 11 23
5 3 24
10 3 24
2 11 24
4 11 24
5 3 25
10 3 25
2 11 25
4 11 25
5 3 26
10 3 26
2 11 26
4 11 26
5 3 27
10 3 27
2 11 27
4 11 27
5 3 28
10 3 28
2 11 28
4 11 28
5 3 29
10 3 29
2 11 29
4 11 29
5 3 30
10 3 30
2 11 30
4 11 30
131 rows selected.
Elapsed: 00:00:01.59
|
|
|
Re: SQL query for a project scheduler [message #631254 is a reply to message #631253] |
Sun, 11 January 2015 15:49 |
Amine
Messages: 371 Registered: March 2010
|
Senior Member |
|
|
First, I would like to thank you for the effort you did.
1. I equate a member to single person. Correct.
2. since there is no "avaiable" weeks per member, I assume that a member is available for all weeks unless they show up in unavailable. Correct.
3. I have also assumed that even though a member may be assigned to multiple projects and could in fact be scheduled for multiple projects in any given week, in order to get higher priority projects done ahead of lower priority projects, each member will be assignd to only one project per week in the actual schedule. Otherwise they become available to put part of their time to lower priority projects at the expense of higher priority projects which is contrary to getting the higher priority projects done first. Correct, and I explained how we deal with this kind of situations.
4. I have also assumed that nb_weeks is a limit to the number of weeks a project can be worked on and is thus an additional rule to check, and not some summary data element noting how many weeks the project has been scheduled for work which would be a desing error in your table designs.
Actually, it was the second idea we ment nb_weeks is the number of weeks the project members fixed to achieve the project.
So for project 1, the members assumed that the project will be delivered after 12 weeks.
5. ** most importantly, you have said that there are "Multiple" solutions to this problem. But this is not really true if my assumptions above are correct. It is that or I have missed something significant in the problem definitino. I was only large on my suppositions. A singleton is at least a set with one element, so I thought in sets
In your result, project with id 1 should appear in 12 weeks, and it's not the case due to your assumption.
Also project 11 is scheduled for only 2 weeks, and it appears more then that.
Thanks again Kevin for the effort. Could you advise me on how to deal with nb_weeks.
Amine
[Updated on: Sun, 11 January 2015 16:01] Report message to a moderator
|
|
|
|
|
|
|
|
|
|
Re: SQL query for a project scheduler [message #631536 is a reply to message #631340] |
Wed, 14 January 2015 13:25 |
Amine
Messages: 371 Registered: March 2010
|
Senior Member |
|
|
Shame on you BlackSwan : really Shame on you !
I am working on a public administration, and I advise all my colleagues to come here to see what people are doing with SQL.
To learn about it, to learn new tricks !
It's not my goal to be a hero in my work. I am the hero of my family, not because of my work, because of who I am !
I had a problem, I model it, issued a first solution of my own, and came here to discuss about solutions or finding problems with the same pattern.
I am not getting a bonus for bringing solutions, this is my work, and the satisfaction stops at me !
That's it. I think you're Evil BlackSwan, your heart is black as you're naming yourself !
Life is not OraFaq, human relations are what make life beautiful, and you seem to hate human relations, as 99% of your posts seem to prove !
[Updated on: Wed, 14 January 2015 13:54] Report message to a moderator
|
|
|
Goto Forum:
Current Time: Mon Mar 18 22:08:26 CDT 2024
|