By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select * from V$VERSION;
| BANNER | BANNER_FULL | BANNER_LEGACY | CON_ID |
|---|---|---|---|
| Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production | Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production Version 21.3.0.0.0 |
Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production | 0 |
Create Table tbl ( EQUIP_ID VarChar2(6), STATUS VarChar2(12), STATUS_START Date, STATUS_END Date );
Insert Into tbl (EQUIP_ID, STATUS, STATUS_START, STATUS_END )
Select '01', 'OUT_SVC', To_Date('07/16/2020', 'mm/dd/yyyy'), To_Date('07/21/2020', 'mm/dd/yyyy') From Dual Union All
Select '01', 'IN_SVC', To_Date('07/21/2020', 'mm/dd/yyyy'), To_Date('07/25/2020', 'mm/dd/yyyy') From Dual;
2 rows affected
WITH
grid ( EQUIP_ID, STATUS, STATUS_START, STATUS_END, STATUS_DATE ) AS
( Select EQUIP_ID, STATUS,
Min(STATUS_START) as STATUS_START,
Max(STATUS_END) as STATUS_END,
Min(STATUS_START) as STATUS_DATE
From tbl
Group By EQUIP_ID, STATUS
UNION ALL
Select EQUIP_ID, STATUS, STATUS_START, STATUS_END,
STATUS_DATE + 1
From grid
Where STATUS_DATE >= STATUS_START And STATUS_DATE < STATUS_END
)
Select Distinct
EQUIP_ID,
Case When Count(STATUS_DATE) Over(Partition By EQUIP_ID, STATUS_DATE) > 1
Then 'IN_SVC'
Else STATUS
End as STATUS,
STATUS_DATE
From grid
Order By STATUS_DATE
| 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 |