By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE device_status AS
SELECT
'01' AS equip_id,
'OUT_SVC' AS status,
date '2020-07-16' AS status_start,
date '2020-07-21' AS status_end
UNION ALL SELECT
'01',
'IN_SVC',
date '2020-07-21',
date '2020-07-25';
2 rows affected
DECLARE
v_first_date DATE;
v_last_date DATE;
BEGIN
SELECT
MIN(LEAST(status_start, status_end)),
MAX(GREATEST(status_start, status_end)) INTO v_first_date, v_last_date
FROM device_status;
EXECUTE IMMEDIATE
'CREATE TABLE DATES AS
SELECT
DATE ''' || TO_CHAR(v_first_date,'YYYY-MM-DD') ||
''' + LEVEL - 1 AS dt
CONNECT BY level <= (
DATE ''' || TO_CHAR(v_last_date,'YYYY-MM-DD') || ''' -
DATE ''' || TO_CHAR(v_first_date,'YYYY-MM-DD') || ''' + 1)';
END;
/
1 rows affected
SELECT * FROM dates ORDER BY dt;
| DT |
|---|
| 16-JUL-20 |
| 17-JUL-20 |
| 18-JUL-20 |
| 19-JUL-20 |
| 20-JUL-20 |
| 21-JUL-20 |
| 22-JUL-20 |
| 23-JUL-20 |
| 24-JUL-20 |
| 25-JUL-20 |
SELECT
ds.equip_id,
ds.status,
d.dt AS status_date
FROM device_status ds
INNER JOIN dates d
ON d.dt BETWEEN ds.status_start AND ds.status_end
ORDER BY
ds.equip_id,
d.dt;
| EQUIP_ID | STATUS | STATUS_DATE |
|---|---|---|
| 01 | OUT_SVC | 16-JUL-20 |
| 01 | OUT_SVC | 17-JUL-20 |
| 01 | OUT_SVC | 18-JUL-20 |
| 01 | OUT_SVC | 19-JUL-20 |
| 01 | OUT_SVC | 20-JUL-20 |
| 01 | OUT_SVC | 21-JUL-20 |
| 01 | IN_SVC | 21-JUL-20 |
| 01 | IN_SVC | 22-JUL-20 |
| 01 | IN_SVC | 23-JUL-20 |
| 01 | IN_SVC | 24-JUL-20 |
| 01 | IN_SVC | 25-JUL-20 |
SELECT
ds.equip_id,
MIN(ds.status) AS status,
d.dt AS status_date
FROM device_status ds
INNER JOIN dates d
ON d.dt BETWEEN ds.status_start AND ds.status_end
GROUP BY
ds.equip_id,
d.dt
ORDER BY
ds.equip_id,
d.dt;
| EQUIP_ID | STATUS | STATUS_DATE |
|---|---|---|
| 01 | OUT_SVC | 16-JUL-20 |
| 01 | OUT_SVC | 17-JUL-20 |
| 01 | OUT_SVC | 18-JUL-20 |
| 01 | OUT_SVC | 19-JUL-20 |
| 01 | OUT_SVC | 20-JUL-20 |
| 01 | IN_SVC | 21-JUL-20 |
| 01 | IN_SVC | 22-JUL-20 |
| 01 | IN_SVC | 23-JUL-20 |
| 01 | IN_SVC | 24-JUL-20 |
| 01 | IN_SVC | 25-JUL-20 |