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  |
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 #304944 is a reply to message #304943] |
Fri, 07 March 2008 04:57   |
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 #304955 is a reply to message #304945] |
Fri, 07 March 2008 05:25   |
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 #305025 is a reply to message #304944] |
Fri, 07 March 2008 10:36   |
 |
Barbara Boehmer
Messages: 9104 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
|
|
|
|
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   |
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 #305628 is a reply to message #305626] |
Tue, 11 March 2008 07:28   |
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 #305710 is a reply to message #305591] |
Tue, 11 March 2008 12:04   |
 |
Michel Cadot
Messages: 68733 Registered: March 2007 Location: Saint-Maur, France, https...
|
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 #306022 is a reply to message #305845] |
Wed, 12 March 2008 10:21   |
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 #306056 is a reply to message #306044] |
Wed, 12 March 2008 12:52   |
 |
Michel Cadot
Messages: 68733 Registered: March 2007 Location: Saint-Maur, France, https...
|
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 #306167 is a reply to message #306103] |
Thu, 13 March 2008 01:56   |
 |
Michel Cadot
Messages: 68733 Registered: March 2007 Location: Saint-Maur, France, https...
|
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 #306248 is a reply to message #306175] |
Thu, 13 March 2008 07:38   |
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 #306378 is a reply to message #306339] |
Fri, 14 March 2008 00:04   |
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
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 #306405 is a reply to message #306388] |
Fri, 14 March 2008 01:58   |
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 #306446 is a reply to message #306438] |
Fri, 14 March 2008 04:40   |
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
|
|
|
|
Goto Forum:
Current Time: Tue Feb 11 10:27:07 CST 2025
|