By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE table_name (EQUIP_ID, STATUS, STATUS_START, STATUS_END) AS
SELECT '01', 'OUT_SVC', DATE '2020-07-16', DATE '2020-07-21' FROM DUAL UNION ALL
SELECT '01', 'IN_SVC', DATE '2020-07-21', DATE '2020-07-25' FROM DUAL
2 rows affected
SELECT t.equip_id,
t.status,
d.status_date
FROM table_name t
CROSS JOIN LATERAL (
SELECT t.status_start + LEVEL - 1 AS status_date
FROM DUAL
CONNECT BY t.status_start + LEVEL - 1 <= t.status_end
) d
| EQUIP_ID | STATUS | STATUS_DATE |
|---|---|---|
| 01 | OUT_SVC | 2020-07-16 00:00:00 |
| 01 | OUT_SVC | 2020-07-17 00:00:00 |
| 01 | OUT_SVC | 2020-07-18 00:00:00 |
| 01 | OUT_SVC | 2020-07-19 00:00:00 |
| 01 | OUT_SVC | 2020-07-20 00:00:00 |
| 01 | OUT_SVC | 2020-07-21 00:00:00 |
| 01 | IN_SVC | 2020-07-21 00:00:00 |
| 01 | IN_SVC | 2020-07-22 00:00:00 |
| 01 | IN_SVC | 2020-07-23 00:00:00 |
| 01 | IN_SVC | 2020-07-24 00:00:00 |
| 01 | IN_SVC | 2020-07-25 00:00:00 |
SELECT t.equip_id,
t.status,
d.status_date
FROM ( SELECT equip_id,
status,
status_start,
status_end,
LEAD(status_end)
OVER (PARTITION BY equip_id ORDER BY status_end) AS next_end
FROM table_name
) t
CROSS JOIN LATERAL (
SELECT t.status_start + LEVEL - 1 AS status_date
FROM DUAL
CONNECT BY t.status_start + LEVEL - 1 < t.status_end
OR ( t.next_end IS NULL
AND t.status_start + LEVEL - 1 = t.status_end )
) d
| EQUIP_ID | STATUS | STATUS_DATE |
|---|---|---|
| 01 | OUT_SVC | 2020-07-16 00:00:00 |
| 01 | OUT_SVC | 2020-07-17 00:00:00 |
| 01 | OUT_SVC | 2020-07-18 00:00:00 |
| 01 | OUT_SVC | 2020-07-19 00:00:00 |
| 01 | OUT_SVC | 2020-07-20 00:00:00 |
| 01 | IN_SVC | 2020-07-21 00:00:00 |
| 01 | IN_SVC | 2020-07-22 00:00:00 |
| 01 | IN_SVC | 2020-07-23 00:00:00 |
| 01 | IN_SVC | 2020-07-24 00:00:00 |
| 01 | IN_SVC | 2020-07-25 00:00:00 |