create table org_props
(id number(5),
ogrn number(15),
inn number(20),
name varchar2(50),
ds date,
de date
);
create table org_props_ext
(id number(5),
status number(5),
address varchar2(100),
eq number (10,2),
ds date,
de date
);
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'));
with t as (
select t1.id, t1.ogrn, t1.inn, t1.name, null status, null address, null eq, t1.ds dt, 1 as which
from t1
union all
select t1.id, t1.ogrn, t1.inn, t1.name, null status, null address, null eq, t1.de dt, 1 as which
from t1
union all
select t2.id, null ogrn, null inn, null name, t2.status, t2.address, t2.eq, t2.ds dt, 2 as which
from t2
union all
select t2.id, null ogrn, null inn, null name, t2.status, t2.address, t2.eq, t2.de dt, 2 as which
from t2)
, t3 as (
select
name,
case
when name is null then lag(name,1) over (order by dt)
else name
end lag_name,
status,
case
when status is null then lag(status,1) over (order by dt)
else status
end lag_status,
address,
case
when address is null then lag(address,1) over (order by dt)
else address
end lag_address,
eq,
case
when eq is null then lag(eq,1) over (order by dt)
else eq
end lag_eg,
dt,
case
when lead(dt,1) over (order by dt) = to_date('31.12.9999','dd.mm.yyyy') then to_date('31.12.9999','dd.mm.yyyy')
else lead(dt,1) over (order by dt) - 1
end as d_end
from t)
--select * from t3
--order by dt
, t4 as (
select
case
when name is null and lag_name is null then lag(lag_name,1) over (order by dt)
when name is null and lag_name is not null then lag_name
else name
end name,
case
when status is null and lag_status is null then lag(lag_status,1) over (order by dt)
when status is null and lag_status is not null then lag_status
else status
end status,
case
when address is null and lag_address is null then lag(lag_address,1) over (order by dt)
when address is null and lag_address is not null then lag_address
else address
end address,
case
when eq is null and lag_eg is null then lag(lag_eg,1) over (order by dt)
when eq is null and lag_eg is not null then lag_eg
else eq
end eq,
dt,
d_end
from t3
--where dt <> d_end
)
--select * from t4
--order by dt
select * from (
select
name, status, address, eq,
min(dt) dstartt, max(d_end) dend
from t4
group by name, status, address, eq
order by min(dt)
where dstart <> dend
Telegram: DB developers channel