Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE ReferenceDateTable 
    (
     ItemId number,
     ReferenceDate date, 
     Type varchar2(1)
    );
INSERT INTO ReferenceDateTable (ItemId, ReferenceDate, Type)
select 1, to_date('19000201', 'YYYYMMDD'), '1' from dual union all
select 1, to_date('19000202', 'YYYYMMDD'), '1' from dual union all
select 1, to_date('19000203', 'YYYYMMDD'), '2' from dual union all
select 1, to_date('19000204', 'YYYYMMDD'), '1' from dual union all
select 1, to_date('19000205', 'YYYYMMDD'), '1' from dual;
5 rows affected

CREATE TABLE ResultTable 
    (
     ItemId number,
     ReferenceDate date, 
     Value number
    );
INSERT INTO ResultTable (ItemId, ReferenceDate, Value)
select 1, to_date('19000201', 'YYYYMMDD'), 1 from dual union all
select 1, to_date('19000202', 'YYYYMMDD'), 2 from dual union all
select 1, to_date('19000203', 'YYYYMMDD'), 3 from dual union all
select 1, to_date('19000204', 'YYYYMMDD'), 4 from dual union all
select 1, to_date('19000205', 'YYYYMMDD'), 5 from dual;
5 rows affected

CREATE TABLE StartDateTable
    (
     ItemId number,
     StartDate date
    );
INSERT INTO StartDateTable (ItemId, StartDate) VALUES (1, to_date('19000101', 'YYYYMMDD'));
1 rows affected

commit;
WITH FirstDateFilter AS (
    SELECT ReferenceDate,
           Type,
           LAG(Type, 1, 0) OVER (ORDER BY ReferenceDate) AS PreviousType
    FROM ReferenceDateTable
    WHERE ItemId = 1
    AND   ReferenceDate <= to_date('19000205', 'YYYYMMDD')
    AND   Type IN ('1', '2')
), SecondDateFilter AS (
    SELECT ReferenceDate
    FROM FirstDateFilter
    WHERE ReferenceDate >= to_date('19000201', 'YYYYMMDD')
    --AND   ReferenceDate >= ( SELECT StartDate FROM StartDateTable WHERE ItemId = 1 )
    AND   Type = '1'
    AND   PreviousType = '1'
)
SELECT ReferenceDate, Value
FROM ResultTable
WHERE ItemId = 1
AND EXISTS ( SELECT * FROM SecondDateFilter WHERE SecondDateFilter.ReferenceDate = ResultTable.ReferenceDate )
;


REFERENCEDATE
VALUE


02-FEB-00
2

05-FEB-00
5


WITH FirstDateFilter AS (
    SELECT ReferenceDate,
           Type,
           LAG(Type, 1, 0) OVER (ORDER BY ReferenceDate) AS PreviousType
    FROM ReferenceDateTable
    WHERE ItemId = 1
    AND   ReferenceDate <= to_date('19000205', 'YYYYMMDD')
    AND   Type IN ('1', '2')
), SecondDateFilter AS (
    SELECT ReferenceDate
    FROM FirstDateFilter
    WHERE ReferenceDate >= to_date('19000201', 'YYYYMMDD')
    AND   ReferenceDate >= ( SELECT StartDate FROM StartDateTable WHERE ItemId = 1 )
    AND   Type = '1'
    AND   PreviousType = '1'
)
SELECT ReferenceDate, Value
FROM ResultTable
WHERE ItemId = 1
AND EXISTS ( SELECT * FROM SecondDateFilter WHERE SecondDateFilter.ReferenceDate = ResultTable.ReferenceDate )
;


REFERENCEDATE
VALUE


01-FEB-00
1

02-FEB-00
2

03-FEB-00
3

04-FEB-00
4

05-FEB-00
5


WITH FirstDateFilter AS (
    SELECT ReferenceDate,
           Type,
           LAG(Type, 1, 0) OVER (ORDER BY ReferenceDate) AS PreviousType
    FROM ReferenceDateTable
    WHERE ItemId = 1
    AND   ReferenceDate <= to_date('19000205', 'YYYYMMDD')
    AND   Type IN ('1', '2')
), SecondDateFilter AS (
    SELECT ReferenceDate
    FROM FirstDateFilter
    WHERE ReferenceDate >= to_date('19000201', 'YYYYMMDD')
    --AND   ReferenceDate >= ( SELECT StartDate FROM StartDateTable WHERE ItemId = 1 )
    AND   Type = '1'
    AND   PreviousType = '1'
)
select * from seconddatefilter;


REFERENCEDATE


02-FEB-00

05-FEB-00


WITH FirstDateFilter AS (
    SELECT ReferenceDate,
           Type,
           LAG(Type, 1, 0) OVER (ORDER BY ReferenceDate) AS PreviousType
    FROM ReferenceDateTable
    WHERE ItemId = 1
    AND   ReferenceDate <= to_date('19000205', 'YYYYMMDD')
    AND   Type IN ('1', '2')
), SecondDateFilter AS (
    SELECT ReferenceDate
    FROM FirstDateFilter
    WHERE ReferenceDate >= to_date('19000201', 'YYYYMMDD')
    AND   ReferenceDate >= ( SELECT StartDate FROM StartDateTable WHERE ItemId = 1 )
    AND   Type = '1'
    AND   PreviousType = '1'
)
select * from seconddatefilter;


REFERENCEDATE


02-FEB-00

05-FEB-00


SELECT referencedate, VALUE
FROM   resulttable rt
WHERE  itemid = 1
AND    EXISTS (SELECT NULL
               FROM   (SELECT referencedate,
                              TYPE,
                              LAG(TYPE, 1, 0) OVER (ORDER BY referencedate) AS previoustype
                       FROM   referencedatetable
                       WHERE  itemid = 1
                       AND    referencedate <= to_date('19000205', 'YYYYMMDD')
                       AND    TYPE IN ('1', '2')) sdf
               WHERE   sdf.referencedate >= to_date('19000201', 'YYYYMMDD')
               --AND     sdf.referencedate >= (SELECT StartDate FROM StartDateTable WHERE ItemId = 1 )
               AND     sdf.TYPE = '1'
               AND     sdf.previoustype = '1'
               AND     sdf.referencedate = rt.referencedate);


REFERENCEDATE
VALUE


02-FEB-00
2

05-FEB-00
5


SELECT referencedate, VALUE
FROM   resulttable rt
WHERE  itemid = 1
AND    EXISTS (SELECT NULL
               FROM   (SELECT referencedate,
                              TYPE,
                              LAG(TYPE, 1, 0) OVER (ORDER BY referencedate) AS previoustype
                       FROM   referencedatetable
                       WHERE  itemid = 1
                       AND    referencedate <= to_date('19000205', 'YYYYMMDD')
                       AND    TYPE IN ('1', '2')) sdf
               WHERE   sdf.referencedate >= to_date('19000201', 'YYYYMMDD')
               AND     sdf.referencedate >= (SELECT StartDate FROM StartDateTable WHERE ItemId = 1 )
               AND     sdf.TYPE = '1'
               AND     sdf.previoustype = '1'
               AND     sdf.referencedate = rt.referencedate);


REFERENCEDATE
VALUE


01-FEB-00
1

02-FEB-00
2

03-FEB-00
3

04-FEB-00
4

05-FEB-00
5