Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table Person
(
personID int
,forename varchar2(50)
,surname varchar2(50)
);
insert all 
into Person (personID, forename, surname) values (1,'AAA','BBB')
into Person (personID, forename, surname) values (2,'CCC','DDD')
select * from dual;
2 rows affected

create table PersonStatus
(
personID int
,fromDate date
,status varchar2(5)
);
insert all
into PersonStatus (personID, fromDate, status) values (1,TO_DATE('2019-01-01','yyyy-mm-dd'),'Q')
into PersonStatus (personID, fromDate, status) values (1,TO_DATE('2019-02-01','yyyy-mm-dd'),'W')
into PersonStatus (personID, fromDate, status) values (1,TO_DATE('2019-03-01','yyyy-mm-dd'),'E')
into PersonStatus (personID, fromDate, status) values (2,TO_DATE('2019-01-01','yyyy-mm-dd'),'R')
into PersonStatus (personID, fromDate, status) values (2,TO_DATE('2019-02-01','yyyy-mm-dd'),'T')
into PersonStatus (personID, fromDate, status) values (2,TO_DATE('2019-03-01','yyyy-mm-dd'),'Y')
select * from dual;
6 rows affected

select * from personstatus ;


PERSONID
FROMDATE
STATUS


1
01-JAN-19
Q

1
01-FEB-19
W

1
01-MAR-19
E

2
01-JAN-19
R

2
01-FEB-19
T

2
01-MAR-19
Y


create table PersonIssue
(
issueID int
,personID int
,issueDate date
);
insert all
into PersonIssue (issueID, personID, issueDate) values (1,1,TO_DATE('2019-01-14','yyyy-mm-dd'))
into PersonIssue (issueID, personID, issueDate) values (2,1,TO_DATE('2019-02-14','yyyy-mm-dd'))
into PersonIssue (issueID, personID, issueDate) values (3,1,TO_DATE('2019-03-14','yyyy-mm-dd'))
into PersonIssue (issueID, personID, issueDate) values (4,2,TO_DATE('2019-01-14','yyyy-mm-dd'))
into PersonIssue (issueID, personID, issueDate) values (5,2,TO_DATE('2019-02-14','yyyy-mm-dd'))
into PersonIssue (issueID, personID, issueDate) values (6,2,TO_DATE('2019-03-14','yyyy-mm-dd'))
select * from dual;
6 rows affected

create or replace view status_view
as
select id as personid, status, from_
, case 
    when to_ is null then sysdate
    else to_ - 1
  end to_
from (
  select 
    personid id
  , fromdate from_
  , lead( fromdate, 1 ) over ( partition by personid order by fromdate ) to_
  , status status
  from personstatus 
) ;
select * from status_view ;


PERSONID
STATUS
FROM_
TO_


1
Q
01-JAN-19
31-JAN-19

1
W
01-FEB-19
28-FEB-19

1
E
01-MAR-19
26-OCT-19

2
R
01-JAN-19
31-JAN-19

2
T
01-FEB-19
28-FEB-19

2
Y
01-MAR-19
26-OCT-19


select 
  p.personID, p.foreName || ' ' || p.surName as person
, pi.issueID as iid
, ps.from_  as statusfrom
, pi.issuedate
, ps.to_ as statusto
, ps.status
from Person p
  join PersonIssue pi 
    on p.personID = pi.personID
  join status_view ps 
    on p.personID = ps.personid
    and pi.issuedate between ps.from_ and ps.to_
order by p.personid, pi.issuedate
;


PERSONID
PERSON
IID
STATUSFROM
ISSUEDATE
STATUSTO
STATUS


1
AAA BBB
1
01-JAN-19
14-JAN-19
31-JAN-19
Q

1
AAA BBB
2
01-FEB-19
14-FEB-19
28-FEB-19
W

1
AAA BBB
3
01-MAR-19
14-MAR-19
26-OCT-19
E

2
CCC DDD
4
01-JAN-19
14-JAN-19
31-JAN-19
R

2
CCC DDD
5
01-FEB-19
14-FEB-19
28-FEB-19
T

2
CCC DDD
6
01-MAR-19
14-MAR-19
26-OCT-19
Y

PERSONID	FROMDATE	STATUS
1	01-JAN-19	Q
1	01-FEB-19	W
1	01-MAR-19	E
2	01-JAN-19	R
2	01-FEB-19	T
2	01-MAR-19	Y

PERSONID	STATUS	FROM_	TO_
1	Q	01-JAN-19	31-JAN-19
1	W	01-FEB-19	28-FEB-19
1	E	01-MAR-19	26-OCT-19
2	R	01-JAN-19	31-JAN-19
2	T	01-FEB-19	28-FEB-19
2	Y	01-MAR-19	26-OCT-19

PERSONID	PERSON	IID	STATUSFROM	ISSUEDATE	STATUSTO	STATUS
1	AAA BBB	1	01-JAN-19	14-JAN-19	31-JAN-19	Q
1	AAA BBB	2	01-FEB-19	14-FEB-19	28-FEB-19	W
1	AAA BBB	3	01-MAR-19	14-MAR-19	26-OCT-19	E
2	CCC DDD	4	01-JAN-19	14-JAN-19	31-JAN-19	R
2	CCC DDD	5	01-FEB-19	14-FEB-19	28-FEB-19	T
2	CCC DDD	6	01-MAR-19	14-MAR-19	26-OCT-19	Y