How to replace start date and end date for multiple row data with the maximum and minimum of date in pl/sql

Question

I have a multiple rows data with unique start date and end date combination.

I want to replace the start date with the minimum of all the start dates and End date with maximum of all the end dates.

Example:

id         start Date    end Date


1005    09/01/2000  05/31/2001

1005    09/05/2000  05/23/2001

100775  03/15/2005  04/30/2005

100775  03/25/2005  04/22/2005

3273    09/01/2003  12/31/2004

3273    09/11/2003  12/11/2004

Now I want the output to look like:

id         start Date    end date


1005    09/01/2000  05/31/2001

1005    09/01/2000  05/31/2001

100775  03/15/2005  04/30/2005

100775  03/15/2005  04/30/2005

3273    09/01/2003  12/31/2004

3273    09/01/2003  12/31/2004

I have tried doing this with max and min functions but that doesn't work since I want this to be only one query for using it in oracle report builder.

Answer 1

You can use just use min and max function to get your desired output:

select
        id,
        min(start_date) OVER (PARTITION BY id)  start_date,
        max(end_date) OVER (PARTITION BY id)  end_date 
FROM table1;

SQL Fiddle Demo