Re: Remove Date overlap duplicates

"Michel Cadot" <micadot{at}altern{dot}org> a écrit dans le message de news: 4aca3a4e$0$1451$426a34cc_at_news.free.fr...
|
| "Shank..." <willbecreated_at_gmail.com> a écrit dans le message de news:
| 22c647e6-f687-4ea1-9991-9fc9102fe760_at_x6g2000prc.googlegroups.com...
|| Hi,
||
|| I have a table containing following data.
||
|| ID Start_date End_date
|| 1 20-OCT-2005 31-DEC-2005
|| 1 01-NOV-2005 31-JAN-2006
|| 2 01-JAN-2005 31-JAN-2005
|| 2 01-JAN-2005 31-DEC-2006
||
|| Now I need to update the start date of 2nd Row as '01-jan-2006' and I
|| also need to delete the 3rd row.
|| In a nut shell, I need to remove the duplicate data either by updating
|| or by deleting.
|| Is there any way to do it using PL/SQL?
|
| Why have you to delete the third row and changing the start date
| of fourth one to '01-jan-2006'?
|
| Regards
| Michel
|

SQL> select * from t order by 1,2,3;

        ID START_DATE END_DATE

---------- ----------- -----------
         1 20-OCT-2005 31-DEC-2005
         1 01-NOV-2005 31-JAN-2006
         2 01-JAN-2005 31-JAN-2005
         2 01-JAN-2005 31-DEC-2006

4 rows selected.

SQL> select id,

  2         greatest(nvl(lag(end_date) over (partition by id order by start_date, end_date)+1,
  3                      start_date),
  4                  start_date) start_date,
  5         end_date

        ID START_DATE END_DATE

---------- ----------- -----------
         1 20-OCT-2005 31-DEC-2005
         1 01-JAN-2006 31-JAN-2006
         2 01-JAN-2005 31-JAN-2005
         2 01-FEB-2005 31-DEC-2006

4 rows selected.

Regards
Michel

|
Received on Mon Oct 05 2009 - 13:34:49 CDT