SQLize | PHPize | SQLtest

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 t1.id, t1.ogrn, t1.inn,t1.name, null status, null address, null eq, t1.ds, null 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 ;