Oracle 18c

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 18c Express Edition Release 18.0.0.0.0 - Production
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
0


create table status (ID,  STATUS) as
    select 1,  'on'     from dual union all
    select 2,  'off'    from dual union all
    select 3,  'pause'  from dual union all
    select 4,  'off'    from dual
4 rows affected

create table statusup (ID, STATUS, UP) as
    select 1, 'on',    date'2021-04-26' from dual union all
    select 1, 'off',   date'2021-04-25' from dual union all
    select 1, 'off',   date'2021-01-24' from dual union all
    select 1, 'on',    date'2021-03-23' from dual union all
    select 2, 'on',    date'2020-04-22' from dual union all
    select 3, 'off',   date'2021-04-26' from dual union all
    select 3, 'on',    date'2021-03-25' from dual union all
    select 3, 'on',    date'2021-01-24' from dual union all
    select 3, 'pause', date'2021-03-23' from dual union all
    select 3, 'off',   date'2019-04-22' from dual union all
    select 4, 'off',   date'2020-04-25' from dual union all
    select 4, 'on',    date'2020-04-21' from dual
12 rows affected

create or replace view statusact as (
select id, status, up,
    lead (up, 1, date'9999-12-31') over (partition by id order by up asc)-(1/24/60/60) till
from statusup)
with param (dt) as (
    select date'2021-05-29' from dual
)    
select id, status, to_char (dt, 'dd-Mon-rr') asofday
from statusact, param
where dt between up and till


ID
STATUS
ASOFDAY


1
on
29-May-21

2
on
29-May-21

3
off
29-May-21

4
off
29-May-21


create or replace package pack as
    type statrec is record (id int, status varchar(8), dt date);
    type stattab is table of statrec;
    function getstatus (dt date) return stattab pipelined;
end;
/
create or replace package body pack as
    function getstatus (dt date) return stattab  pipelined is
    begin 
        for r in (  
            select id, status 
            from (select id, status, up, lead (up, 1, date'9999-12-31') over (
                    partition by id order by up asc)-(1/24/60/60) till
                from statusup) where dt between up and till) loop
            pipe row (statrec (r.id, r.status, dt));    
        end loop;        
        return;
    end;
end;
/
select id, status, to_char (dt, 'dd-Mon-rr') asofday
from pack.getstatus (date'2021-05-29')


ID
STATUS
ASOFDAY


1
on
29-May-21

2
on
29-May-21

3
off
29-May-21

4
off
29-May-21