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.