By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE mytable(
slno INTEGER NOT NULL PRIMARY KEY
,Emp_no INTEGER NOT NULL
,leave_appl_dt DATE NOT NULL
,From_dt DATE NOT NULL
,To_dt DATE NOT NULL
,no_of_days INTEGER NOT NULL
);
INSERT INTO mytable(slno,Emp_no,leave_appl_dt,From_dt,To_dt,no_of_days) VALUES (1,1001,'2020-01-01','2020-01-01','2020-01-12',12);
INSERT INTO mytable(slno,Emp_no,leave_appl_dt,From_dt,To_dt,no_of_days) VALUES (2,1002,'2020-01-10','2020-01-15','2020-01-25',10);
2 rows affected
select * from mytable
| slno | Emp_no | leave_appl_dt | From_dt | To_dt | no_of_days |
|---|---|---|---|---|---|
| 1 | 1001 | 2020-01-01 | 2020-01-01 | 2020-01-12 | 12 |
| 2 | 1002 | 2020-01-10 | 2020-01-15 | 2020-01-25 | 10 |
with leaves as (
select emp_no, leave_appl_dt, from_dt, to_dt
from mytable
union all
select emp_no, leave_appl_dt, dateadd(day, 1, from_dt), to_dt
from leaves
where from_dt < to_dt
)
select from_dt leave_dt, emp_no, leave_appl_dt from leaves order by emp_no, leave_dt
| leave_dt | emp_no | leave_appl_dt |
|---|---|---|
| 2020-01-01 | 1001 | 2020-01-01 |
| 2020-01-02 | 1001 | 2020-01-01 |
| 2020-01-03 | 1001 | 2020-01-01 |
| 2020-01-04 | 1001 | 2020-01-01 |
| 2020-01-05 | 1001 | 2020-01-01 |
| 2020-01-06 | 1001 | 2020-01-01 |
| 2020-01-07 | 1001 | 2020-01-01 |
| 2020-01-08 | 1001 | 2020-01-01 |
| 2020-01-09 | 1001 | 2020-01-01 |
| 2020-01-10 | 1001 | 2020-01-01 |
| 2020-01-11 | 1001 | 2020-01-01 |
| 2020-01-12 | 1001 | 2020-01-01 |
| 2020-01-15 | 1002 | 2020-01-10 |
| 2020-01-16 | 1002 | 2020-01-10 |
| 2020-01-17 | 1002 | 2020-01-10 |
| 2020-01-18 | 1002 | 2020-01-10 |
| 2020-01-19 | 1002 | 2020-01-10 |
| 2020-01-20 | 1002 | 2020-01-10 |
| 2020-01-21 | 1002 | 2020-01-10 |
| 2020-01-22 | 1002 | 2020-01-10 |
| 2020-01-23 | 1002 | 2020-01-10 |
| 2020-01-24 | 1002 | 2020-01-10 |
| 2020-01-25 | 1002 | 2020-01-10 |