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;