Skip to Main Content

SQL & PL/SQL

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!

How to get days between dates by excluding the overlapping dates ?

947663May 5 2023

Dear Expert,

I need to find out the no of days the person on leave but some leaves are overlapping with other leaves due to configuration which allows user to apply overlapping leaves.

In this sample data as below. The expected leave days is 9 but if I sum up it gives me 11 days.

create table ramesh_employee_tbl (emp_no varchar2(15),start_date date,end_date date,leave_type varchar2(100));

insert into ramesh_employee_tbl values ('100',to_date('01-May-2023','DD-Mon-YYYY'),to_date('03-May-2023','DD-Mon-YYYY'),'Unpaid Leave');
insert into ramesh_employee_tbl values ('100',to_date('02-May-2023','DD-Mon-YYYY'),to_date('04-May-2023','DD-Mon-YYYY'),'Personal Leave');
insert into ramesh_employee_tbl values ('100',to_date('06-May-2023','DD-Mon-YYYY'),to_date('07-May-2023','DD-Mon-YYYY'),'Unpaid Leave');
insert into ramesh_employee_tbl values ('100',to_date('10-May-2023','DD-Mon-YYYY'),to_date('12-May-2023','DD-Mon-YYYY'),'Unpaid Leave');

Query tried out is :

=======

select sum((end_date-start_date)+1) from ramesh_employee_tbl group by emp_no;

But Personal leave is overlapping with Unpaid Leave.

Thanks,

Ramesh S.

This post has been answered by Solomon Yakobson on May 5 2023
Jump to Answer
Comments
Post Details
Added on May 5 2023
13 comments
693 views