Home » SQL & PL/SQL » SQL & PL/SQL » Problem with analytical functions
Problem with analytical functions [message #227398] Wed, 28 March 2007 02:48 Go to next message
friendarora
Messages: 24
Registered: February 2007
Junior Member

hi,

I have a table data like

name start_date end_date
M 10-Mar-2006 15-Mar-2006
M 16-Mar-2006 20-Mar-2006
N 21-Mar-2006 25-Mar-2006
M 26-Mar-2006 28-Mar-2006
M 29-Mar-2006 30-Mar-2006

the expected output is
name start_date end_date
M 10-Mar-2006 20-Mar-2006
N 21-Mar-2006 25-Mar-2006
M 26-Mar-2006 30-Mar-2006

but when trying quiery like

select distinct name,min(start_date)over(partition by name order by start_date,end_date), max(end_date)over(partition by name order by start_date,end_date) from table

i get output as
name start_date end_date
M 10-Mar-2006 30-Mar-2006
N 21-Mar-2006 25-Mar-2006


can u please advice how to get the desired output

thanks in advance


Re: Problem with analytical functions [message #227401 is a reply to message #227398] Wed, 28 March 2007 03:01 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
plz send me sql scripts

thanks
srinivas
Re: Problem with analytical functions [message #227424 is a reply to message #227398] Wed, 28 March 2007 03:51 Go to previous messageGo to next message
friendarora
Messages: 24
Registered: February 2007
Junior Member

create table emp (name varchar2(10),start_date date, end_date date);

insert into emp values ('M','10-Mar-2006','15-Mar-2006');
insert into emp values ('M','16-Mar-2006','20-Mar-2006');
insert into emp values ('N','21-Mar-2006','25-Mar-2006');
insert into emp values ('M','26-Mar-2006','28-Mar-2006');
insert into emp values ('M','10-Mar-2006','15-Mar-2006');
insert into emp values ('M','29-Mar-2006','30-Mar-2006');


and the query used is
select distinct name,min(start_date)over(partition by name order by start_date,end_date), max(end_date)over(partition by name order by start_date,end_date) from emp;

[Updated on: Wed, 28 March 2007 03:51]

Report message to a moderator

Re: Problem with analytical functions [message #227438 is a reply to message #227424] Wed, 28 March 2007 04:32 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
u can use lead and leg functions
thanks
srinivas
Re: Problem with analytical functions [message #227441 is a reply to message #227438] Wed, 28 March 2007 04:50 Go to previous messageGo to next message
friendarora
Messages: 24
Registered: February 2007
Junior Member

please can u give me the soluton as i am not able to figure out how to use lead and lag functions in this issue.
Re: Problem with analytical functions [message #227442 is a reply to message #227441] Wed, 28 March 2007 04:52 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Check this solution from Thomas Kyte.
Re: Problem with analytical functions [message #227456 is a reply to message #227424] Wed, 28 March 2007 05:21 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Assuming there is no overlap between rows for same name (that is your row before the last one of your exemple is a wrong one):
SQL> with 
  2    data as (
  3      select name, start_date, end_date,
  4             case 
  5             when nvl(start_date-lag(end_date) over(partition by name order by start_date),0)
  6                  != 1
  7               then row_number() over (order by name, start_date)
  8             end rn
  9      from emp
 10    ),
 11    grouping as (
 12      select name, start_date, end_date,
 13             max(rn) over (partition by name order by start_date) grp
 14      from data
 15    )
 16  select name, min(start_date) start_date, max(end_date) end_date
 17  from grouping
 18  group by name, grp
 19  order by 2, name
 20  /
NAME       START_DATE  END_DATE
---------- ----------- -----------
M          10-Mar-2006 20-Mar-2006
N          21-Mar-2006 25-Mar-2006
M          26-Mar-2006 30-Mar-2006

3 rows selected.

Regards
Michel
Previous Topic: Tuning of the Procedure
Next Topic: Load Data
Goto Forum:
  


Current Time: Sat Dec 03 08:22:05 CST 2016

Total time taken to generate the page: 0.09409 seconds