Below is my input code
ID EMP_ID PROJECT_NAME START_DATE END_DATE
1 10016351 ABC 22-12-2016 15-05-2017
2 10016351 ABC 01-09-2016 22-11-2016
1 10081503 RTBS AMaaS 21-11-2016 15-02-2017
2 10081503 RTBS AMaaS 18-07-2016 25-11-2016
3 10081503 RTBS AMaaS 21-08-2016 13-10-2016
4 10081503 RTBS AMaaS 03-02-2015 22-05-2015
1 10089293 RTBS PDaaS 17-02-2017 31-12-2017
2 10089293 RTBS PDaaS 13-06-2016 14-02-2017
I need to check for an employee, dates are overlapping or not. If yes then it will return min Start date and Max end date and flag as 'O'.
For ex, for emp_id = 10081503 dates are overlapping 21-11-2016 is falling between 18-07-2016 and 25-11-2016, also 21-08-2016 is falling between 18-07-2016 and 25-11-2016 so the query should return start date as 18-07-2016 (min) and end date as 15-02-2017 (max) for the dates which are overlapping. For rest entries, it should return the same entry with flag 'N'.
EMP_ID | PROJECT_NAME | START_DATE | END_DATE | Flag 10081503 | RTBS AMaaS | 21-11-2016 | 15-02-2017 | O 10081503 | RTBS AMaaS | 03-02-2015 | 22-05-2015 | N
Like wise my final output should be,
ID EMP_ID PROJECT_NAME START_DATE END_DATE FLAG
1 10016351 ABC 22-12-2016 15-05-2017 N
2 10016351 ABC 01-09-2016 22-11-2016 N
1 10081503 RTBS AMaaS 21-11-2016 15-02-2017 O
2 10081503 RTBS AMaaS 03-02-2015 22-05-2015 N
1 10089293 RTBS PDaaS 17-02-2017 31-12-2017 N
2 10089293 RTBS PDaaS 13-06-2016 14-02-2017 N