Skip to Main Content

SQL Developer

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Remove dups with overlapping date

ramagiriMar 21 2017 — edited Mar 21 2017

Hi,

looking for SQL help.

remove the dupliates on ID and end_date. This is very simple but the challenge I've to select one row out of below duplicates and that does not overlap with previous end date

record

ID  START_DATE  END_DATE   COMMENTS

99  25-NOV-13   23-JAN-16  TEST

99  21-APR-14   31-DEC-00  OVERLAPPING DATE WITH THE ABOVE ROW AND END DATE DUPLICATED FOR ID 99. DO NOT SELECT THIS ROW

99  24-JAN-16   31-DEC-00  TESTING

85  20-DEC-13   25-FEB-16  TESTING

85  26-FEB-16   31-DEC-00  TESTING

34  18-SEP-12   28-MAR-13  TESTING

34  29-MAR-13   04-APR-14  TESTING

34  05-APR-14   31-DEC-00  TESTING

34  02-APR-14   31-DEC-00  OVERLAPPING DATE WITH ABOVE ROW AND END DATE DUPLICATED FOR ID 34. DO NOT SELECT THIS ROW

Expected output

ID  START_DATE  END_DATE   COMMENTS

99  25-NOV-13   23-JAN-16  TEST

99  24-JAN-16   31-DEC-00  TESTING

85  20-DEC-13   25-FEB-16  TESTING

85  26-FEB-16   31-DEC-00  TESTING

34  18-SEP-12   28-MAR-13  TESTING

34  29-MAR-13   04-APR-14  TESTING

34  05-APR-14   31-DEC-00  TESTING

CREATE TABLE TEST(ID VARCHAR2(30),START_DATE DATE,END_DATE DATE,COMMENTS VARCHAR2(300));

Insert into TEST

   (ID, START_DATE, END_DATE, COMMENTS)

Values

   ('99', TO_DATE('11/25/2013', 'MM/DD/YYYY'), TO_DATE('01/23/2016', 'MM/DD/YYYY'), 'TEST');

Insert into TEST

    (ID, START_DATE, END_DATE, COMMENTS)

  Values

   ('99', TO_DATE('04/21/2014', 'MM/DD/YYYY'), TO_DATE('31/12/4000', 'MM/DD/YYYY'), 'OVERLAPPING DATE WITH THE BELOW ROW AND END DATE DUPLICATED FOR ID 99. DO NOT SELECT THIS ROW');

 

Insert into TEST

   (ID, START_DATE, END_DATE, COMMENTS)

Values

   ('99', TO_DATE('04/24/2016', 'MM/DD/YYYY'), TO_DATE('31/12/4000', 'MM/DD/YYYY'), 'TESTING');

 

 

Insert into TEST

    (ID, START_DATE, END_DATE, COMMENTS)

  Values

   (85', TO_DATE('12/20/2013', 'MM/DD/YYYY'), TO_DATE('02/25/2016', 'MM/DD/YYYY'), 'TESTING');

 

  Insert into TEST

     (ID, START_DATE, END_DATE, COMMENTS)

   Values

   ('85', TO_DATE('02/26/2016', 'MM/DD/YYYY'), TO_DATE('31/12/4000', 'MM/DD/YYYY'), 'TESTING');

 

 

   Insert into TEST

      (ID, START_DATE, END_DATE, COMMENTS)

    Values

   ('34', TO_DATE('09/18/2012', 'MM/DD/YYYY'), TO_DATE('03/28/2013', 'MM/DD/YYYY'), 'TESTING');

   Insert into TEST

      (ID, START_DATE, END_DATE, COMMENTS)

    Values

   ('34', TO_DATE('03/29/2013', 'MM/DD/YYYY'), TO_DATE('04/04/2014', 'MM/DD/YYYY'), 'TESTING');

   Insert into TEST

      (ID, START_DATE, END_DATE, COMMENTS)

    Values

   ('34', TO_DATE('04/05/2014', 'MM/DD/YYYY'), TO_DATE('31/12/4000', 'MM/DD/YYYY'), 'TESTING');

   Insert into TEST

      (ID, START_DATE, END_DATE, COMMENTS)

    Values

   ('34', TO_DATE('04/02/2014', 'MM/DD/YYYY'), TO_DATE('31/12/4000', 'MM/DD/YYYY'), 'OVERLAPPING DATE WITH ABOVE ROW AND END DATE DUPLICATED FOR ID 34. DO NOT SELECT THIS ROW');

 

   COMMIT;

This post has been answered by unknown-7404 on Mar 21 2017
Jump to Answer

Comments

Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Apr 18 2017
Added on Mar 21 2017
1 comment
176 views