© 2019 Rank & State Algorithm by Jörg & Christine Schmidt. Just for trying out. No warranty.
create view product_2 as with
product as
(
select *,
row_number() over (partition by product_id
order by registered_from) as rankReg,
row_number() over (partition by product_id
order by valid_from, registered_from) as rankVal
from product_1
),
productExt as
(
select product_id, rankReg, rankVal
from product
union
select product_id, 0, 0
from product
where rankReg = 1
),
tbl as
(
select b.product_id, b.rankReg, b.rankVal,
a.rankVal as rankValPre
from productExt a join productExt b
on a.product_id = b.product_id
and a.rankReg + 1 = b.rankReg
),
head as
(
select a.product_id, a.rankReg, b.rankVal,
a.rankReg - 1 as state,
row_number() over(partition by a.product_id, b.rankVal
order by a.rankReg) as rankRegrank
from tbl a join productExt b
on a.product_id = b.product_id
and b.rankVal between a.rankValPre and a.rankVal - 1
),
tail as
(
select a.product_id, a.rankReg as rankRegTo, b.rankVal,
row_number() over(partition by a.product_id, b.rankVal
order by a.rankReg) as rankRegTorank
from tbl a join productExt b on a.product_id = b.product_id
and b.rankVal between a.rankVal and a.rankValPre - 1
),
tbl2 as
(
select a.product_id, a.state,
a.rankReg,
case when b.rankRegTo is null then 0
else b.rankRegTo
end as rankRegTo,
min(a.rankVal) as rankVal, max(a.rankVal) + 1 as rankValTo
from head a left join tail b on a.product_id = b.product_id
and a.state > 0
and a.rankVal = b.rankVal
and a.rankRegrank = b.rankRegTorank
group by a.product_id, a.state, a.rankReg, b.rankRegTo
union
select product_id, rankReg as state,
rankReg, rankReg + 1 as rankRegTo,
rankVal, 0 as rankValTo
from product
)
select a.product_id, b.registered_from,
case when c.registered_from is null then '9999-12-31'
else c.registered_from
end as registered_to,
d.valid_from,
case when e.valid_from is null then '9999-12-31'
else e.valid_from
end as valid_to,
f.price
from tbl2 a
inner join product b on a.product_id = b.product_id
and a.rankReg = b.rankReg
left join product c on a.product_id = c.product_id
and a.rankRegTo = c.rankReg
inner join product d on a.product_id = d.product_id
and a.rankVal = d.rankVal
left join product e on a.product_id = e.product_id
and a.rankValTo = e.rankVal
inner join product f on a.product_id = f.product_id
and a.state = f.rankReg;
