Home » SQL & PL/SQL » SQL & PL/SQL » checking concesutive record's dates to find whether it completed within 3 days (merged)
checking concesutive record's dates to find whether it completed within 3 days (merged) [message #304936] Fri, 07 March 2008 04:33 Go to next message
sangisiva
Messages: 17
Registered: March 2008
Location: India
Junior Member
Hi,

I have a table as below


ID Action start_date finish_date ACT_level

23 Idea 02-oct-2006 02-oct-2006 1
23 decisioncharter 02-oct-2006 02-oct-2006 2
23 analysis 02-oct-2006 04-oct-2006 3
23 Design 02-oct-2006 02-oct-2006 4
23 code 02-oct-2006 22-oct-2006 5
12 Idea 03-jan-2007 03-jan-2007 1
12 decision charter 03-jan-2007 03-jan-2007 2
12 analysis 03-jan-2007 04-jan-2008 3
12 Design 04-mar-2008 02-jun-2008 4
12 code 22-oct-2008 22-oct-2008 5
111 Idea 2-oct-2008 12-oct-2008 1
111 decision charter 12-oct-2008 13-oct-2008 2
111 analysis 12-oct-2008 14-oct-2008 3
111 Design 22-oct-2008 22-oct-2008 4
111 code 22-oct-2008 22-oct-2008 5
122 Idea 02-nov-2006 22-nov-2006 1
122 decision charter 02-nov-2006 22-nov-2006 2
122 analysis 02-nov-2006 04-dec-2006 3
122 Design 02-nov-2006 02-dec-2006 4
122 code 02-nov-2006 22-dec-2006 5





I have to write a select query to fins out for which id there are more than 3 actions completed consecutively within 3 days.

I mean have to take the first records finish date and chek with further records date to fins out is there any consecutive action completed that too within 3 days

For eg...
IDs
23,12,111



[Updated on: Fri, 07 March 2008 04:43]

Report message to a moderator

Re: Need help on comparing adjacent records [message #304941 is a reply to message #304936] Fri, 07 March 2008 04:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post a test case: create table and insert statements.
Also post the result you want with these data.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Use the "Preview Message" button to verify.
Also post your Oracle version (4 decimals).

Regards
Michel
Re: Need help on comparing adjacent records [message #304943 is a reply to message #304936] Fri, 07 March 2008 04:51 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Search for lead analytic functions. Also please follow guidelines provided by Michael.

Regards

Raj
Re: Need help on comparing adjacent records [message #304944 is a reply to message #304943] Fri, 07 March 2008 04:57 Go to previous messageGo to next message
sangisiva
Messages: 17
Registered: March 2008
Location: India
Junior Member
I already tried with lead function.

Can you please tell me how can I specify the expressions in partition by clause

select
id,
action,
ACT_level,
count(action) over(order by finish_date range between current row and interval '3' day following) "Number of Actions"
from Action

It is returning no records...
I am new to analytical function... so could not figure it out the issue...
Re: Need help on comparing adjacent records [message #304945 is a reply to message #304944] Fri, 07 March 2008 04:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post the test case as requested and you will have more help.

Regards
Michel
Re: Need help on comparing adjacent records [message #304955 is a reply to message #304945] Fri, 07 March 2008 05:25 Go to previous messageGo to next message
sangisiva
Messages: 17
Registered: March 2008
Location: India
Junior Member

CREATE TABLE Action
(Id NUMBER(3),
Action VARCHAR(25),
Start_date DATE,
Finish_date DATE,
Act_level NUMBER(3))


INSERT INTO Action
VALUES (23,'Idea',to_date('02-oct-2006','DD-Mon-YYYY'),to_date('02-oct-2006','DD-Mon-YYYY'),1)

INSERT INTO Action
VALUES (23,'Dcision Charter',to_date('02-oct-2006','DD-Mon-YYYY'),to_date('02-oct-2006','DD-Mon-YYYY'),2)

INSERT INTO Action
VALUES (23,'Analysis',to_date('02-oct-2006','DD-Mon-YYYY'),to_date('02-oct-2006','DD-Mon-YYYY'),3)

INSERT INTO Action
VALUES (23,'Design',to_date('02-oct-2006','DD-Mon-YYYY'),to_date('02-oct-2006','DD-Mon-YYYY'),4).....

[Updated on: Fri, 07 March 2008 05:29]

Report message to a moderator

Re: Need help on comparing adjacent records [message #304964 is a reply to message #304955] Fri, 07 March 2008 06:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
A FULL test case and with the result you want with it.
AND FORMAT IT.

Regards$
Michel
Re: Need help on comparing adjacent records [message #305005 is a reply to message #304944] Fri, 07 March 2008 08:29 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Quote:
I already tried with lead function.
Show us what code you used when you tried with the lead function.
Re: Need help on comparing adjacent records [message #305025 is a reply to message #304944] Fri, 07 March 2008 10:36 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
sangisiva wrote on Fri, 07 March 2008 02:57
I already tried with lead function.

Can you please tell me how can I specify the expressions in partition by clause

select
id,
action,
ACT_level,
count(action) over(order by finish_date range between current row and interval '3' day following) "Number of Actions"
from Action

It is returning no records...
I am new to analytical function... so could not figure it out the issue...


count(action) over(PARTITION BY ID order by finish_date range between current row and interval '3' day following) "Number of Actions"

[Updated on: Fri, 07 March 2008 10:37]

Report message to a moderator

Re: Need help on comparing adjacent records [message #305076 is a reply to message #305025] Sat, 08 March 2008 03:15 Go to previous messageGo to next message
sangisiva
Messages: 17
Registered: March 2008
Location: India
Junior Member
Smile Thanks Barbara.

It works.
Need help on - checking concesutive record's dates to find whether it completed within 3 days [message #305591 is a reply to message #304936] Tue, 11 March 2008 05:54 Go to previous messageGo to next message
sangisiva
Messages: 17
Registered: March 2008
Location: India
Junior Member
Hi,

I have tables as
create table project
(
prj_id varchar2(15),prj_name varchar2(40),status varchar2(15))

and
create table action
(
prj_id varchar2(15),
task_name varchar2(50),
finish_date date,
task_seq    number(3))


Values
insert into project values ('PRJ1','Project1','Active')
insert into project values ('PRJ2','Project2','Completed')
insert into project values ('PRJ3','Project3','Active')
insert into project values ('PRJ4','Project4','Closed')
insert into project values ('PRJ5','Project5','Active')


insert into action values ('PRJ1','Idea',to_date('18/mar/2007','DD/Mon/YY'),1)
insert into action values ('PRJ1','Decision',to_date('18/mar/2007','DD/Mon/YY'),2)
insert into action values ('PRJ1','Sup',to_date('18/jan/2008','DD/Mon/YY'),17)
insert into action values ('PRJ1','QQQ',to_date('7/feb/2008','DD/Mon/YY'),18)
insert into action values ('PRJ1','Con1',to_date('18/mar/2007','DD/Mon/YY'),3)
insert into action values ('PRJ1','Con2',to_date('18/mar/2007','DD/Mon/YY'),4)
insert into action values ('PRJ1','Pre1',to_date('8/aug/2007','DD/Mon/YY'),5)
insert into action values ('PRJ1','Pre2',to_date('16/oct/2007','DD/Mon/YY'),6)
insert into action values ('PRJ1','Dev',to_date('26/oct/2007','DD/Mon/YY'),7)
insert into action values ('PRJ1','Pro1',to_date('7/nov/2007','DD/Mon/YY'),9)
insert into action values ('PRJ1','Pro2',to_date('7/nov/2007','DD/Mon/YY'),10)
insert into action values ('PRJ1','Pro3',to_date('7/nov/2007','DD/Mon/YY'),11)
insert into action values ('PRJ1','Desdev',to_date('31/oct/2007','DD/Mon/YY'),8)
insert into action values ('PRJ1','Mic',to_date('12/nov/2007','DD/Mon/YY'),12)
insert into action values ('PRJ1','Cp',to_date('8/nov/2007','DD/Mon/YY'),13)
insert into action values ('PRJ1','Rel',to_date('18/jan/2008','DD/Mon/YY'),14)
insert into action values ('PRJ1','Inv',to_date('18/jan/2008','DD/Mon/YY'),15)
insert into action values ('PRJ1','Mark',to_date('18/jan/2008','DD/Mon/YY'),16)



What I need to do is have to find the group of active projexts who's tasks (more than 3 tasks) completed concecutively within 3 days. Task_seq which gives the sequence number of tasks

I need my result as


Prj_id First_Task Last_Task No_of_Tasks
PRJ1    Idea      Con2      4
PRJ1    Pro1      Pro3      3
PRJ1    Cp        Sup       4



Here I have just taken 1 project in action table. Like the same I have to find for all the Active Projects

[Updated on: Tue, 11 March 2008 05:56]

Report message to a moderator

Re: Need help on - checking concesutive record's dates to find whether it completed within 3 days [message #305606 is a reply to message #305591] Tue, 11 March 2008 06:20 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
http://www.orafaq.com/forum/t/98555/94420/

I would like to know in which way it is different from what you have requested before ?

Regards

Raj
Re: Need help on - checking concesutive record's dates to find whether it completed within 3 days [message #305613 is a reply to message #305606] Tue, 11 March 2008 06:32 Go to previous messageGo to next message
sangisiva
Messages: 17
Registered: March 2008
Location: India
Junior Member
It is Different.

Here I need to extract all the occurrences of more than 3 concecutive actions completed within 3 days...

Re: Need help on - checking concesutive record's dates to find whether it completed within 3 days [message #305614 is a reply to message #305613] Tue, 11 March 2008 06:34 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Good luck.

Regards
Re: Need help on - checking concesutive record's dates to find whether it completed within 3 days [message #305624 is a reply to message #305613] Tue, 11 March 2008 07:05 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Quote:
It is Different.

Quote:
I have to write a select query to fins out for which id there are more than 3 actions completed consecutively within 3 days.

Quote:
Here I need to extract all the occurrences of more than 3 concecutive actions completed within 3 days...

Seems very similar to me
Re: Need help on - checking concesutive record's dates to find whether it completed within 3 days [message #305626 is a reply to message #305624] Tue, 11 March 2008 07:22 Go to previous messageGo to next message
sangisiva
Messages: 17
Registered: March 2008
Location: India
Junior Member
I used...

select  prj_id,
task_name,
finish_date,
count(task_name) over(order by finish_date range between 
current row and interval '3' day following)"Consecutive tasks"
 from action



Gives
PRJ_ID	TASK_NAME	FINISH_DATE	Consecutive tasks
PRJ1	Idea		3/18/2007	4
PRJ1	Decision	3/18/2007	4
PRJ1	Con1		3/18/2007	4
PRJ1	Con2		3/18/2007	4
PRJ1	Pre1		8/8/2007	1
PRJ1	Pre2		10/16/2007	1
PRJ1	Dev		10/26/2007	1
PRJ1	Desdev		10/31/2007	1
PRJ1	Pro1		11/7/2007	4
PRJ1	Pro2		11/7/2007	4
PRJ1	Pro3		11/7/2007	4
PRJ1	Cp		11/8/2007	1
PRJ1	Mic		11/12/2007	1
PRJ1	Rel		1/18/2008	4
PRJ1	Inv		1/18/2008	4
PRJ1	Mark		1/18/2008	4
PRJ1	Sup		1/18/2008	4
PRJ1	QQQ		2/7/2008	1


trying to get the result which I mentioned in my first post...

I can group by date and consecutive tasks to find out the number of occurrence... but how can I get all the fields (PRJ_ID, TASK_NAME,FINISH_DATE,Consecutive tasks) when I use group by...

More over this grouping would work if the concecutive tasks have same date.... but if it has different dates say

PRJ_ID	TASK_NAME	FINISH_DATE	Consecutive tasks
PRJ1	Idea		3/18/2007	4
PRJ1	Decision	3/19/2007	4
PRJ1	Con1		3/19/2007	4
PRJ1	Con2		3/20/2007	4

I can not use this logic to get the desired result....

Hope this explains my issue... Sad
Re: Need help on - checking concesutive record's dates to find whether it completed within 3 days [message #305628 is a reply to message #305626] Tue, 11 March 2008 07:28 Go to previous messageGo to next message
sangisiva
Messages: 17
Registered: March 2008
Location: India
Junior Member
I tried this too...

select  prj_id,
task_name,
finish_date,
round(lead(finish_date,1) over(order by task_seq)-finish_date)
"Day difference"
from action


Gives
PRJ_ID	TASK_NAME	FINISH_DATE	Day difference
PRJ1	Idea		3/18/2007	0
PRJ1	Decision	3/18/2007	0
PRJ1	Con1		3/18/2007	0
PRJ1	Con2		3/18/2007	143
PRJ1	Pre1		8/8/2007	69
PRJ1	Pre2		10/16/2007	10
PRJ1	Dev		10/26/2007	5
PRJ1	Desdev		10/31/2007	7
PRJ1	Pro1		11/7/2007	0
PRJ1	Pro2		11/7/2007	0
PRJ1	Pro3		11/7/2007	5
PRJ1	Mic		11/12/2007	-4
PRJ1	Cp		11/8/2007	71
PRJ1	Rel		1/18/2008	0
PRJ1	Inv		1/18/2008	0
PRJ1	Mark		1/18/2008	0
PRJ1	Sup		1/18/2008	20
PRJ1	QQQ		2/7/2008	




But could not go further...

Re: Need help on - checking concesutive record's dates to find whether it completed within 3 days [message #305630 is a reply to message #305628] Tue, 11 March 2008 07:34 Go to previous messageGo to next message
sangisiva
Messages: 17
Registered: March 2008
Location: India
Junior Member
For each set of consecutive tasks,

I have to take the
Prj_id,
First task (sort the task sequence and get the first task which is started in that set)
Last task (which is finished last for the set)
NoofTasks (number of tasks of that set)

So , the result would be as...

Prj_id First_Task Last_Task No_of_Tasks
PRJ1    Idea      Con2      4
PRJ1    Pro1      Pro3      3
PRJ1    Cp        Sup       4



Hope I am clear...
Re: Need help on - checking concesutive record's dates to find whether it completed within 3 days [message #305665 is a reply to message #305630] Tue, 11 March 2008 08:48 Go to previous messageGo to next message
sangisiva
Messages: 17
Registered: March 2008
Location: India
Junior Member
Am I not clear still ?

Re: Need help on - checking concesutive record's dates to find whether it completed within 3 days [message #305710 is a reply to message #305591] Tue, 11 March 2008 12:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
With the data you gave in the message I reply (#305591) "Cp" is part of group "Pro1"->"Pro3" and is its last element (starts 7-Nov and ends 8-Nov), so this group has 4 tasks.
Next group starts with "Rel" and ends with "Sup" all in 18-Jan and has 4 tasks too.
"Cp" can't be part of the last group as it ends on 8-Nov far from 18-Jan.

With these remarks I found this one:
SQL> with 
  2    group_start as (
  3      select finish_date, task_seq
  4      from action
  5      where task_seq in ( select min(task_seq) 
  6                          from ( select task_seq, level lvl
  7                                 from action a
  8                                 connect by finish_date > prior finish_date + 3
  9                                 start with task_seq = 1
 10                               )
 11                          group by lvl
 12                        )
 13    ),
 14    data as (
 15      select prj_id, task_name, finish_date, task_seq,
 16             (select task_seq from group_start 
                 where group_start.task_seq = action.task_seq) grp
 17      from action
 18    ),
 19    grouping as (
 20      select prj_id, task_name, finish_date, task_seq,
 21             max(grp) over (order by finish_date, task_seq) grp
 22      from data
 23    ),
 24    counting as (
 25      select prj_id, finish_date, task_seq,
 26             first_value (task_name) over (partition by grp order by task_seq) first_task,
 27             last_value (task_name) over (partition by grp order by task_seq) last_task,
 28             row_number () over (partition by grp order by task_seq) rn,
 29             count(*) over (partition by grp) nb_tasks
 30      from grouping
 31    )
 32  select prj_id, first_task, last_task, nb_tasks
 33  from counting
 34  where nb_tasks > 3
 35    and rn = nb_tasks
 36  order by finish_date, task_seq
 37  /
PRJ_ID     FIRST_TASK LAST_TASK    NB_TASKS
---------- ---------- ---------- ----------
PRJ1       Idea       Con2                4
PRJ1       Pro1       Cp                  4
PRJ1       Rel        Sup                 4

3 rows selected.

It currently works for only one project. I let you improve it for any number (clue: connect by no more start at 1 but at first row for each project, each analytic function must be partition by project id).

Regards
Michel

[Edit: split line 16 too long]

[Updated on: Tue, 11 March 2008 14:36]

Report message to a moderator

Re: checking concesutive record's dates to find whether it completed within 3 days (merged) [message #305845 is a reply to message #304936] Wed, 12 March 2008 03:30 Go to previous messageGo to next message
sangisiva
Messages: 17
Registered: March 2008
Location: India
Junior Member
Hi Michel,

You are right...

Quote:
"Cp" can't be part of the last group as it ends on 8-Nov far from 18-Jan.


Sorry for the confusion... Cp won't be there in the last group..

Thanks a Bunch for your effort and reply...This is what I wanted...

I will try out the same and will let you know..

Again Thanks a Lot for the help. Smile

[Updated on: Wed, 12 March 2008 03:31]

Report message to a moderator

Re: checking concesutive record's dates to find whether it completed within 3 days (merged) [message #306022 is a reply to message #305845] Wed, 12 March 2008 10:21 Go to previous messageGo to next message
zozogirl
Messages: 77
Registered: November 2005
Location: Seoul, Korea
Member

I don't think Michel's solution is so clever.
This problem is so called 'Sequential Group query' as i coined the term.
(refer to my website : http://www.soqool.com/servlet/board?cmd=view&cat=100&subcat=1010&seq=522)

So, this problem can be solved as following:


SELECT   PRJ_ID,
         MIN (TASK_NAME)KEEP (DENSE_RANK FIRST 
                        ORDER BY FINISH_DATE, TASK_SEQ) FIRST_TASK,
         MIN (TASK_NAME)KEEP (DENSE_RANK LAST 
                        ORDER BY FINISH_DATE, TASK_SEQ) LAST_TASK,
         COUNT (*) NO_OF_TASKS
    FROM (SELECT B.*,
                 SUM (FLAG) OVER (PARTITION BY PRJ_ID 
                                  ORDER BY FINISH_DATE, TASK_SEQ) GRP
            FROM (SELECT A.*,
                         DECODE
                            (  FINISH_DATE
                             - LAG (FINISH_DATE) OVER (PARTITION BY PRJ_ID 
                                                       ORDER BY FINISH_DATE, TASK_SEQ),
                             0, 0, 1, 0, 2, 0,
                             1
                            ) FLAG
                    FROM ACTION A) B)
GROUP BY PRJ_ID, GRP
  HAVING COUNT (*) > 3  -- which is right, >3 or >=3?
ORDER BY PRJ_ID, GRP



PRJ_ID     FIRST_TASK LAST_TASK  NO_OF_TASKS
---------- ---------- ---------- -----------
PRJ1       Idea       Con2                 4
PRJ1       Pro1       Cp                   4
PRJ1       Rel        Sup                  4

[Updated on: Wed, 12 March 2008 11:36] by Moderator

Report message to a moderator

Re: checking concesutive record's dates to find whether it completed within 3 days (merged) [message #306044 is a reply to message #306022] Wed, 12 March 2008 11:40 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
I'd love to be able to read your links, but I cannot display Korean characters. I know one of the Franks have asked in the past; is there a way to translate it?
I am wondering how many people here can actually read your site. I am sure it would be well worth it.
Re: checking concesutive record's dates to find whether it completed within 3 days (merged) [message #306056 is a reply to message #306044] Wed, 12 March 2008 12:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I think link it just ad to his/her site.

I also think zozogirl's query is not so clever.
For instance, if I add 2 new rows to build a new group (New1,New2,Mic):
insert into action values ('PRJ1','New1',to_date('10/nov/2007','DD/Mon/YY'),19);
insert into action values ('PRJ1','New2',to_date('11/nov/2007','DD/Mon/YY'),20);

SQL> select * from action order by prj_id, finish_date, task_seq;
PRJ_ID     TASK_NAME  FINISH_DAT   TASK_SEQ
---------- ---------- ---------- ----------
PRJ1       Idea       18/03/2007          1
PRJ1       Decision   18/03/2007          2
PRJ1       Con1       18/03/2007          3
PRJ1       Con2       18/03/2007          4
PRJ1       Pre1       08/08/2007          5
PRJ1       Pre2       16/10/2007          6
PRJ1       Dev        26/10/2007          7
PRJ1       Desdev     31/10/2007          8
PRJ1       Pro1       07/11/2007          9
PRJ1       Pro2       07/11/2007         10
PRJ1       Pro3       07/11/2007         11
PRJ1       Cp         08/11/2007         13
PRJ1       New1       10/11/2007         19
PRJ1       New2       11/11/2007         20
PRJ1       Mic        12/11/2007         12
PRJ1       Rel        18/01/2008         14
PRJ1       Inv        18/01/2008         15
PRJ1       Mark       18/01/2008         16
PRJ1       Sup        18/01/2008         17
PRJ1       QQQ        07/02/2008         18

20 rows selected.

You query gives (modifying >3 to >=3 to handle 3 members group as you suggested):
SQL> SELECT   PRJ_ID,
  2           MIN (TASK_NAME)KEEP (DENSE_RANK FIRST 
  3                          ORDER BY FINISH_DATE, TASK_SEQ) FIRST_TASK,
  4           MIN (TASK_NAME)KEEP (DENSE_RANK LAST 
  5                          ORDER BY FINISH_DATE, TASK_SEQ) LAST_TASK,
  6           COUNT (*) NO_OF_TASKS
  7      FROM (SELECT B.*,
  8                   SUM (FLAG) OVER (PARTITION BY PRJ_ID 
  9                                    ORDER BY FINISH_DATE, TASK_SEQ) GRP
 10              FROM (SELECT A.*,
 11                           DECODE
 12                              (  FINISH_DATE
 13                               - LAG (FINISH_DATE) OVER (PARTITION BY PRJ_ID 
 14                                                         ORDER BY FINISH_DATE, TASK_SEQ),
 15                               0, 0, 1, 0, 2, 0,
 16                               1
 17                              ) FLAG
 18                      FROM ACTION A) B)
 19  GROUP BY PRJ_ID, GRP
 20    HAVING COUNT (*) >= 3
 21  ORDER BY PRJ_ID, GRP
 22  /
PRJ_ID     FIRST_TASK LAST_TASK  NO_OF_TASKS
---------- ---------- ---------- -----------
PRJ1       Idea       Con2                 4
PRJ1       Pro1       Mic                  7
PRJ1       Rel        Sup                  4

Which is wrong.
The second group is actually 2 groups: (Pro1,Pro2,Pro3,Cp) for 7/11 and 8/11, and (New1,New2,CP) for 10 to 12/11.

I slighty modified my query to improve the first part and better handle the task_seq disorder (regarding finish_date order):
SQL> with 
  2    group_start as (
  3      select finish_date, task_seq, rownum rn
  4      from ( select task_seq, finish_date,
  5                     min(case when lvl < prev_lvl then rownum end) over () rn
  6              from ( select task_seq, finish_date, level lvl, 
  7                            lag (level) over (order by null) prev_lvl
  8                     from (select * from action order by finish_date, task_seq)
  9                     connect by finish_date >= prior finish_date + 3
 10                     start with task_seq = 1
 11                   )
 12           )
 13      where rownum < rn
 14    ),
 15    data as (
 16      select prj_id, task_name, finish_date, task_seq,
 17             (select rn from group_start 
 18              where group_start.task_seq = action.task_seq) grp
 19      from action
 20    ),
 21    grouping as (
 22      select prj_id, task_name, finish_date, task_seq,
 23             max(grp) over (order by finish_date, task_seq) grp
 24      from data
 25    ),
 26    counting as (
 27      select prj_id, finish_date, task_seq,
 28             first_value (task_name) over (partition by grp order by task_seq) first_task,
 29             last_value (task_name) over (partition by grp order by task_seq) last_task,
 30             row_number () over (partition by grp order by task_seq) rn,
 31             count(*) over (partition by grp) nb_tasks
 32      from grouping
 33    )
 34  select prj_id, first_task, last_task, nb_tasks
 35  from counting
 36  where nb_tasks >= 3
 37    and rn = nb_tasks
 38  order by finish_date, task_seq
 39  /
PRJ_ID     FIRST_TASK LAST_TASK    NB_TASKS
---------- ---------- ---------- ----------
PRJ1       Idea       Con2                4
PRJ1       Pro1       Cp                  4
PRJ1       Mic        New2                3
PRJ1       Rel        Sup                 4

4 rows selected.

Regards
Michel
Re: checking concesutive record's dates to find whether it completed within 3 days (merged) [message #306103 is a reply to message #305845] Wed, 12 March 2008 16:43 Go to previous messageGo to next message
zozogirl
Messages: 77
Registered: November 2005
Location: Seoul, Korea
Member

So, now, whose query is right?
What is the correct logic?

Re: checking concesutive record's dates to find whether it completed within 3 days (merged) [message #306167 is a reply to message #306103] Thu, 13 March 2008 01:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't know it's a matter of interpretation of OP's sentence:
Quote:
query to fins out for which id there are more than 3 actions completed consecutively within 3 days.

I think it as when a 3 days period is over another one starts with the next row.

With your help I improved the last part of my query (that I think was rather bad):
SQL> with 
  2    group_start as (
  3      select finish_date, task_seq, rownum rn
  4      from ( select task_seq, finish_date,
  5                     min(case when lvl < prev_lvl then rownum end) over () rn
  6              from ( select task_seq, finish_date, level lvl, 
  7                             lag (level) over (order by null) prev_lvl
  8                     from (select * from action order by finish_date, task_seq)
  9                     connect by finish_date >= prior finish_date + 3
 10                     start with task_seq = 1
 11                   )
 12           )
 13      where rownum < rn
 14    ),
 15    data as (
 16      select prj_id, task_name, finish_date, task_seq,
 17             (select rn from group_start 
 18              where group_start.task_seq = action.task_seq) grp
 19      from action
 20    ),
 21    grouping as (
 22      select prj_id, task_name, finish_date, task_seq,
 23             max(grp) over (order by finish_date, task_seq) grp
 24      from data
 25    )
 26  select prj_id, 
 27         min(task_name) keep (dense_rank first order by task_seq) first_task, 
 28         min(task_name) keep (dense_rank last order by task_seq) last_task, 
 29         count(*) nb_tasks
 30  from grouping
 31  group by prj_id, grp
 32  having count(*) >= 3
 33  order by grp
 34  /
PRJ_ID     FIRST_TASK LAST_TASK    NB_TASKS
---------- ---------- ---------- ----------
PRJ1       Idea       Con2                4
PRJ1       Pro1       Cp                  4
PRJ1       Mic        New2                3
PRJ1       Rel        Sup                 4

4 rows selected.

But it also depends on how "first task" and "last task" are interpreted. Order by task sequence (my query) or by finish date (your query).

Regards
Michel

[Updated on: Thu, 13 March 2008 03:01]

Report message to a moderator

Re: checking concesutive record's dates to find whether it completed within 3 days (merged) [message #306175 is a reply to message #306167] Thu, 13 March 2008 02:36 Go to previous messageGo to next message
zozogirl
Messages: 77
Registered: November 2005
Location: Seoul, Korea
Member

Yes, you are right.
Correct logic is needed.

Re: checking concesutive record's dates to find whether it completed within 3 days (merged) [message #306248 is a reply to message #306175] Thu, 13 March 2008 07:38 Go to previous messageGo to next message
sangisiva
Messages: 17
Registered: March 2008
Location: India
Junior Member
Sorry friends.. It is my mistake that I have not included task sequence in my previous examples...


Here goes Michel's example...

PRJ_ID     TASK_NAME  FINISH_DAT   TASK_SEQ
---------- ---------- ---------- ----------
PRJ1       Idea       18/03/2007          1
PRJ1       Decision   18/03/2007          2
PRJ1       Con1       18/03/2007          3
PRJ1       Con2       18/03/2007          4
PRJ1       Pre1       08/08/2007          5
PRJ1       Pre2       16/10/2007          6
PRJ1       Dev        26/10/2007          7
PRJ1       Desdev     31/10/2007          8
PRJ1       Pro1       07/11/2007          9
PRJ1       Pro2       07/11/2007         10
PRJ1       Pro3       07/11/2007         11
PRJ1       Cp         08/11/2007         13
PRJ1       New1       10/11/2007         19
PRJ1       New2       11/11/2007         20
PRJ1       Mic        12/11/2007         12
PRJ1       Rel        18/01/2008         14
PRJ1       Inv        18/01/2008         15
PRJ1       Mark       18/01/2008         16
PRJ1       Sup        18/01/2008         17
PRJ1       QQQ        07/02/2008         18



My result would be as -- Order by Task_seq

PRJ_ID     TASK_NAME  FINISH_DAT   TASK_SEQ
---------- ---------- ---------- ----------
PRJ1       Idea       18/03/2007          1
PRJ1       Decision   18/03/2007          2
PRJ1       Con1       18/03/2007          3
PRJ1       Con2       18/03/2007          4
PRJ1       Pre1       08/08/2007          5
PRJ1       Pre2       16/10/2007          6
PRJ1       Dev        26/10/2007          7
PRJ1       Desdev     31/10/2007          8
PRJ1       Pro1       07/11/2007          9
PRJ1       Pro2       07/11/2007         10
PRJ1       Pro3       07/11/2007         11
PRJ1       Mic        12/11/2007         12
PRJ1       Cp         08/11/2007         13
PRJ1       Rel        18/01/2008         14
PRJ1       Inv        18/01/2008         15
PRJ1       Mark       18/01/2008         16
PRJ1       Sup        18/01/2008         17
PRJ1       QQQ        07/02/2008         18
PRJ1       New1       10/11/2007         19
PRJ1       New2       11/11/2007         20


Result as ...

PRJ_ID     FIRST_TASK LAST_TASK    NB_TASKS
---------- ---------- ---------- ----------
PRJ1       Idea       Con2                4
PRJ1       Pro1       pro3                3 
PRJ1       Rel        Sup                 4


CP won't come in the second set as it is not consecutive as per task sequence (which is 13)

[Updated on: Thu, 13 March 2008 08:07]

Report message to a moderator

Re: checking concesutive record's dates to find whether it completed within 3 days (merged) [message #306284 is a reply to message #306248] Thu, 13 March 2008 09:49 Go to previous messageGo to next message
zozogirl
Messages: 77
Registered: November 2005
Location: Seoul, Korea
Member

Then what if we add the followings?

PRJ1       Add1       11/11/2007         21
PRJ1       Add2       12/11/2007         22

[Updated on: Thu, 13 March 2008 09:50]

Report message to a moderator

Re: checking concesutive record's dates to find whether it completed within 3 days (merged) [message #306325 is a reply to message #306284] Thu, 13 March 2008 12:03 Go to previous messageGo to next message
sangisiva
Messages: 17
Registered: March 2008
Location: India
Junior Member
PRJ1       New1       10/11/2007         19
PRJ1       New2       11/11/2007         20
PRJ1       Add1       11/11/2007         21
PRJ1       Add2       12/11/2007         22


we will have one more set

as

PRJ1       New1       ADD2         4


have to look for >=3 consecutive tasks (consecutive tasks means sort it based on task sequence) completed with in 3 days...
Re: checking concesutive record's dates to find whether it completed within 3 days (merged) [message #306328 is a reply to message #306325] Thu, 13 March 2008 12:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
But what if these tasks finished 3 days before (7/11 to 9/11)?

Regards
Michel
Re: checking concesutive record's dates to find whether it completed within 3 days (merged) [message #306339 is a reply to message #306325] Thu, 13 March 2008 14:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Also how does it come if you have a new task (Add3, seq=23) that finishes 1 (or 2) day after Add2?

Regards
Michel
Re: checking concesutive record's dates to find whether it completed within 3 days (merged) [message #306378 is a reply to message #306339] Fri, 14 March 2008 00:04 Go to previous messageGo to next message
sangisiva
Messages: 17
Registered: March 2008
Location: India
Junior Member
Michel Cadot wrote on Fri, 14 March 2008 01:17
Also how does it come if you have a new task (Add3, seq=23) that finishes 1 (or 2) day after Add2?

Regards
Michel



PRJ1       New1       10/11/2007         19
PRJ1       New2       11/11/2007         20
PRJ1       Add1       11/11/2007         21
PRJ1       Add2       12/11/2007         22
PRJ1	   Add3       13/11/2007         23



PRJ1       New1       ADD2         5


No of tasks would be just increased by 1...

In this set, there are more than 3 (there are 5 tasks) consecutive tasks (as per task sequence) completed within 3 days or less...

For comparison, need to consider the previous task just before the task we compare... day difference b/w Add2 and Add3 is just 1 day.. so we can count it for this set...
Re: checking concesutive record's dates to find whether it completed within 3 days (merged) [message #306388 is a reply to message #306378] Fri, 14 March 2008 01:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There are inconsistencies in what you say:
New1->Add2 is 4 tasks, New1->Add3 is 5 tasks but between New1 and Add3 there are more than 3 days and only 3 between New2 and Add3.

So what should be the result?
And if they finished 3 days earlier?

Regards
Michel

[Updated on: Fri, 14 March 2008 01:03]

Report message to a moderator

Re: checking concesutive record's dates to find whether it completed within 3 days (merged) [message #306405 is a reply to message #306388] Fri, 14 March 2008 01:58 Go to previous messageGo to next message
sangisiva
Messages: 17
Registered: March 2008
Location: India
Junior Member
Hope you are comparing dates with the first task's date...

we have to compare the adjacent dates...

Difference - 

B/w New1-New2 is 1
B/w New2-Add1 is 1
B/w Add1-Add2 is 0
B/w Add2-Add3 is 1

these records are consecutive as per task sequence.



Quote:
And if they finished 3 days earlier?


Even I am not sure abt it... I have to inquire it with my team as in some records later task has earlier dates which is consecutive in task sequence.
Re: checking concesutive record's dates to find whether it completed within 3 days (merged) [message #306417 is a reply to message #306405] Fri, 14 March 2008 02:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I still don't understand.
What is this new comparison with adjacent dates? There is no mention of this before just a period of 3 days.
So what is the result? Is Add3 is the group or not? Why?

Quote:
in some records later task has earlier dates which is consecutive in task sequence.

This is a key point. You have 2 interlaced groups with same finish dates (seq 9-11 and 19-22/23).

Regards
Michel
Re: checking concesutive record's dates to find whether it completed within 3 days (merged) [message #306432 is a reply to message #306417] Fri, 14 March 2008 03:16 Go to previous messageGo to next message
sangisiva
Messages: 17
Registered: March 2008
Location: India
Junior Member
Add3 will be in the group.



Quote:

PRJ1       New1       ADD2         5




sorry, there was a mistake in the previous post...

it should be as

PRJ1       New1       ADD3         5

[Updated on: Fri, 14 March 2008 03:21]

Report message to a moderator

Re: checking concesutive record's dates to find whether it completed within 3 days (merged) [message #306438 is a reply to message #306432] Fri, 14 March 2008 03:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
But Add3 is not in the 3 days period.
Do you mean the period should more or less 3 days but there must be 3 consecutive tasks in the first 3 days and all addition days must be consecutive.
Is this the specification?

Regards
Michel
Re: checking concesutive record's dates to find whether it completed within 3 days (merged) [message #306446 is a reply to message #306438] Fri, 14 March 2008 04:40 Go to previous messageGo to next message
sangisiva
Messages: 17
Registered: March 2008
Location: India
Junior Member
Michel Cadot wrote on Fri, 14 March 2008 14:22
But Add3 is not in the 3 days period.
Do you mean the period should more or less 3 days but there must be 3 consecutive tasks in the first 3 days and all addition days must be consecutive.
Is this the specification?

Regards
Michel



period should be 3 or less days but there must be more than 3 consecutive tasks

I slightly modified zozo's query which gives me the desired result...and for the point we discussed (later task with earlier dates) - waiting to get the confirmation from the team

SELECT   PRJ_ID,
         MIN (TASK_NAME)KEEP (DENSE_RANK FIRST 
                        ORDER BY  TASK_SEQ) FIRST_TASK,
         MIN (TASK_NAME)KEEP (DENSE_RANK LAST 
                        ORDER BY TASK_SEQ) LAST_TASK,
         COUNT (*) NO_OF_TASKS
    FROM (SELECT B.*,
                 SUM (FLAG) OVER (PARTITION BY PRJ_ID 
                                  ORDER BY TASK_SEQ) GRP
            FROM (SELECT A.*,
                         DECODE
                            (  FINISH_DATE
                             - LAG (FINISH_DATE) OVER (PARTITION BY PRJ_ID 
                                                       ORDER BY TASK_SEQ),
                             0, 0, 1, 0, 2, 0,
                             1
                            ) FLAG
                    FROM ACTION A) B)
GROUP BY PRJ_ID, GRP
  HAVING COUNT (*) >= 3  -- which is right, >3 or >=3?
ORDER BY PRJ_ID, GRP


I just removed the finish_date in ORDER BY....

so far I have not used connect by start with ...now I am learning it by working on your Query.... will keep you posted once I done with your query...








[Updated on: Fri, 14 March 2008 04:54]

Report message to a moderator

Re: checking concesutive record's dates to find whether it completed within 3 days (merged) [message #306464 is a reply to message #306446] Fri, 14 March 2008 07:08 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
period should be 3 or less days but there must be more than 3 consecutive tasks

So Add3 should not be in the group as it is in the 4th day.

Also accordingly to the last part of your sentence, the following should not be an elected group:
PRJ1       Pro1       pro3                3

As there are only 3 tasks and not more than 3.

Regards
Michel
Previous Topic: Procedure
Next Topic: Parse string from CSV using Utl_file read
Goto Forum:
  


Current Time: Sun Dec 04 08:16:35 CST 2016

Total time taken to generate the page: 0.09673 seconds