create table t1
(id number(5),
ogrn number(15),
inn number(20),
name varchar2(50),
ds date,
de date
);
create table t2
(id number(5),
status varchar2(5),
address varchar2(100),
eq number (10,2),
ds date,
de date
);
INSERT INTO t1(id, ogrn, inn, name, ds, de)
VALUES(125, 1127847448520, 7810880684, 'ООО "ЛЕН-РЕЗЕРВ"', to_date('01.01.2017','dd.mm.yyyy'), to_date('30.06.2018','dd.mm.yyyy'));
INSERT INTO t1(id, ogrn, inn, name, ds, de)
VALUES(125, 1127847448520, 7810880684, 'ООО "ЛЕНТЕХ-РЕЗЕРВ"', to_date('01.07.2018','dd.mm.yyyy'), to_date('31.12.9999','dd.mm.yyyy'));
INSERT INTO t2(id, status, address, eq, ds, de)
VALUES(125, '001', 'СПб Шуваловский 22', 10000, to_date('05.01.2017','dd.mm.yyyy'), to_date('10.09.2018','dd.mm.yyyy'));
INSERT INTO t2(id, status, address, eq, ds, de)
VALUES(125, '101', 'СПб Просвещения 130', 10000, to_date('11.09.2018','dd.mm.yyyy'), to_date('31.12.9999','dd.mm.yyyy'));
select
with s as (
select t1.id, t1.ogrn, t1.inn,t1.name, null status, null address, null eq, t1.ds, null de
first_value(t2.ds) over (order by t2.ds) de
from t1, t2
where t1.id = t2.id
and t1.ds < t2.ds
and t1.de < t2.ds
union all
select t1.id, t1.ogrn, t1.inn,t1.name, t2.status, t2.address, t2.eq
, case when t1.ds<t2.ds then t2.ds else t1.ds end as dstart
, case when t1.de>t2.de then t2.de else t1.de end as dend
from t1
inner join t2 on t1.id=t2.id and t1.ds<t2.de and t2.ds<t1.de
)
s.*,
case
when de is null then lead(de, 1) over (order by de) - 1
else de
end dend
from s
Telegram: DB developers channel