1 Reply Latest reply on Mar 21, 2017 8:23 PM by rp0428

    Remove dups with overlapping date

    ramagiri

      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;