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 Go to next message
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 #631114 is a reply to message #631100] Fri, 09 January 2015 09:05 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
> but it is not giving the right result.
My car won't go.
Tell me how to make my car go.
Statement above is less than useless because we have no idea what should be the "right results".
Re: SQL query for a project scheduler [message #631117 is a reply to message #631114] Fri, 09 January 2015 10:06 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
I may have missed it, but your data design seems to be missing one or more important data elements.

What is the start week of each project?
What is the start date of each member on each project?


Also, there are maybe some rules that were not defined.

Can members work on more than one project at a time?  The model suggests they can which is OK by me but since there is no indication of % of work, that suggests not.

Lastly your solution design is ambiguous.

Is it a requirement that you get just one query that gives the result or can you use a process to generate it?
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?

Kevin

Re: SQL query for a project scheduler [message #631156 is a reply to message #631114] Fri, 09 January 2015 14:40 Go to previous messageGo to next message
Amine
Messages: 371
Registered: March 2010
Senior Member

BlackSwan wrote on Fri, 09 January 2015 16:05
> but it is not giving the right result.
My car won't go.
Tell me how to make my car go.
Statement above is less than useless because we have no idea what should be the "right results".


I think you have a problem with your personality BlackSwan : always above people ! But don't you think that if we were all the same, all with a high level, like you for example, then what will be the purpose of life !?
Re: SQL query for a project scheduler [message #631158 is a reply to message #631156] Fri, 09 January 2015 14:50 Go to previous messageGo to next message
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 Smile.

Thanks in advance,
Amine
Re: SQL query for a project scheduler [message #631161 is a reply to message #631158] Fri, 09 January 2015 14:55 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>we are looking for the best one.

which metric at what value measures "best"?
Explain how to differentiate the "best" from the rest.
Re: SQL query for a project scheduler [message #631163 is a reply to message #631161] Fri, 09 January 2015 15:13 Go to previous messageGo to next message
Amine
Messages: 371
Registered: March 2010
Senior Member

Here are the measures :
1. The priority of the project : we can not schedule the first week a project of priority 10. Obviously, we have to start with priority 1 to make the project ends as soon as possible.

Here are the constraints :
1. Alternate between projects schedule to let the projects go on in a parallel way.
2. Two projects a week
3. A project is scheduled if and only if there are 2 available members of the project.
Re: SQL query for a project scheduler [message #631165 is a reply to message #631163] Fri, 09 January 2015 15:32 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
What does "best" solution look like?
What does non-best solution look like?
Why is non-best solution not considered the best solution?
What needs to change in order for the next non-best solution to be considered the best?

How will you, I or anyone recognize when the "best" solution gets posted; as opposed to sub-optimal solution?
Re: SQL query for a project scheduler [message #631191 is a reply to message #631165] Sat, 10 January 2015 02:03 Go to previous messageGo to next message
Amine
Messages: 371
Registered: March 2010
Senior Member

Ok. Let's find a solution first, I think this is doable. And I am here to discuss "solutions" not necessarily the best one at first. Don't be pessimistic : if we want it, we'll do it. And, once again, I am here for that.
Re: SQL query for a project scheduler [message #631217 is a reply to message #631191] Sat, 10 January 2015 08:31 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Please read and follow the forum guidlines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

Including #10!
Re: SQL query for a project scheduler [message #631250 is a reply to message #631217] Sun, 11 January 2015 13:10 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
Making one more assumption.

I assume that even though a member can work on more than one project at a time, once the member is actually targeted in a solution to work on a project, we do not want that member to work on a project of lower priority. Otherwise they would be taking time away from a higher priority project and giving to a lower priority project.

I also assume that MEMBER=SINGLE PERSON, and not A GROUP OF PEOPLE.

Kevin
Re: SQL query for a project scheduler [message #631252 is a reply to message #631250] Sun, 11 January 2015 15:18 Go to previous messageGo to next message
Amine
Messages: 371
Registered: March 2010
Senior Member

Hopefully, you're here Kevin Smile

1st assumption : that's correct. Even if a person is involved in more than one project at a time, it (the member)
will work on the project of high priority. Actually, members that work on high priority projects are experienced members.
Meaning they can "unlock" a situation on a project of less priority and be back to the project of high priority.

2nd assumption : that's correct too. A member is a physical person and not a group of persons.

Thanks again Kevin,
Amine
Re: SQL query for a project scheduler [message #631253 is a reply to message #631250] Sun, 11 January 2015 15:27 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Smile 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 Smile

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 #631274 is a reply to message #631254] Mon, 12 January 2015 05:07 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
Well, I guess there is a bug somewhere. Your job is be to find it and correct it. If this is beyond your abilities, there is no shame in that, but that would mean this solution is not for you.

Good luck. Kevin
Re: SQL query for a project scheduler [message #631285 is a reply to message #631274] Mon, 12 January 2015 06:24 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
FYI, I have debugged it. The problem was in the nb_weeks existential sub-query as you noted. Get the correct existential sub-query and the problem will go away.

Kevin

Re: SQL query for a project scheduler [message #631332 is a reply to message #631285] Mon, 12 January 2015 14:15 Go to previous messageGo to next message
Amine
Messages: 371
Registered: March 2010
Senior Member

Could you explain me why the problem is in the nb_weeks column ?
Re: SQL query for a project scheduler [message #631336 is a reply to message #631332] Mon, 12 January 2015 14:50 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
the existential sub-query is wrong. It is counting the wrong thing. This was supposed to implement the rule that a project could not be scheduled for more than nb_weeks number of weeks. But that is not what this sub-query is counting. There is also a second problem with the sub-query but it would be obvious for me to point it out whereas you are supposed to be doing some debugging of your own and trying to understand this code if you intend to use it.

This is wrong. You need to understand the logical rule being applied, then translate that into sql. I used an existential sub-query to do it but I made two mistakes in this sub-query. Find them. The best way to do that is to ignore the code and instead concentrate on the logical rule and how it might be tested. Then you can write your own code for it and compare to this sub-query.

                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
                               )
Re: SQL query for a project scheduler [message #631338 is a reply to message #631336] Mon, 12 January 2015 14:56 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
> Then you can write your own code
Assume facts which have NO supporting evidence in this or any previous thread started by OP.
If somebody will write the code, Amine will use it & appear to be the hero.
I wish I could convince others to do my job for me while still collecting a paycheck for what amounts to code piracy.
Re: SQL query for a project scheduler [message #631339 is a reply to message #631338] Mon, 12 January 2015 14:58 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
maybe my bugs were not all accidents? hmmm
Re: SQL query for a project scheduler [message #631340 is a reply to message #631339] Mon, 12 January 2015 15:04 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Amine's previous savior was Barbara Boehmer; until she burned out feeding it answers to never ending questions about how to code X, Y, Z, & on & on.

You can lead some folks to knowledge, but you can't make them think.
Re: SQL query for a project scheduler [message #631536 is a reply to message #631340] Wed, 14 January 2015 13:25 Go to previous message
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

Previous Topic: Handle duplicate Configurations (merged)
Next Topic: pl sql to XML conversion
Goto Forum:
  


Current Time: Mon Mar 18 22:08:26 CDT 2024