Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE elbat
             (code integer,
              startdate date,
              enddate date,
              unit varchar2(5),
              codestatus varchar2(8));
INSERT INTO elbat
            (code,
             startdate,
             enddate,
             unit,
             codestatus)
            SELECT 1001,
                   to_date('20100101', 'YYYYMMDD'),
                   to_date('20101231', 'YYYYMMDD'),
                   'UnitA',
                   'Active'
                   FROM dual
            UNION ALL
            SELECT 1001,
                   to_date('20110101', 'YYYYMMDD'),
                   to_date('20151231', 'YYYYMMDD'),
                   'UnitB',
                   'Active'
                   FROM dual
            UNION ALL
            SELECT 1001,
                   to_date('20160101', 'YYYYMMDD'),
                   to_date('21000101', 'YYYYMMDD'),
                   'UnitB',
                   'Inactive'
                   FROM dual
            UNION ALL
            SELECT 1002,
                   to_date('20160101', 'YYYYMMDD'),
                   to_date('20181231', 'YYYYMMDD'),
                   'UnitA',
                   'Active'
                   FROM dual
            UNION ALL
            SELECT 1002,
                   to_date('20190101', 'YYYYMMDD'),
                   to_date('21000101', 'YYYYMMDD'),
                   'UnitA',
                   'Inactive'
                   FROM dual
            UNION ALL
            SELECT 1003,
                   to_date('20140101', 'YYYYMMDD'),
                   to_date('21000101', 'YYYYMMDD'),
                   'UnitC',
                   'Active'
                   FROM dual;
6 rows affected

SELECT code,
       to_char(min(CASE
                    WHEN codestatus = 'Active' THEN
                      startdate
                   END), 'YYYYMMDD') startdate,
       to_char(min(CASE
                    WHEN codestatus = 'Active' THEN
                      enddate
                   END), 'YYYYMMDD') enddate,
       max(CASE
             WHEN startdate <= sysdate
                  AND enddate > sysdate THEN
               codestatus
           END) status
       FROM elbat
       GROUP BY code
       ORDER BY code;


CODE
STARTDATE
ENDDATE
STATUS


1001
20100101
20101231
Inactive

1002
20160101
20181231
Inactive

1003
20140101
21000101
Active
CODE	STARTDATE	ENDDATE	STATUS
1001	20100101	20101231	Inactive
1002	20160101	20181231	Inactive
1003	20140101	21000101	Active