SQL Code to Generate Two-Dimensional Time Intervals

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
Share this:
