SQL Code to Generate Two-Dimensional Time Intervals

© 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;

Derivation of the SQL code

%d bloggers like this: