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 rangReg,
    row_number() over (partition by product_id 
        order by valid_from, registered_from) as rangVal
from product_1
),
productExt as 
(
select product_id, rangReg, rangVal 
from product
union
select product_id, 0, 0 
from product 
where rangReg = 1
),
tbl as 
(
select b.product_id, b.rangReg, b.rangVal, a.rangVal as rangValPre
from productExt a join productExt b on a.product_id = b.product_id
    and a.rangReg + 1 = b.rangReg
),
head as
(
select a.product_id, a.rangReg, b.rangVal, a.rangReg - 1 as state,
    row_number() over(partition by a.product_id, b.rangVal 
        order by a.rangReg) as rangRegRang
from tbl a join productExt b on a.product_id = b.product_id
    and b.rangVal between a.rangValPre and a.rangVal - 1
),
tail as 
(
select a.product_id, a.rangReg as rangRegTo, b.rangVal,
    row_number() over(partition by a.product_id, b.rangVal 
        order by a.rangReg) as rangRegToRang
from tbl a join productExt b on a.product_id = b.product_id
    and b.rangVal between a.rangVal and a.rangValPre - 1
),
tbl2 as 
(
select a.product_id, a.state,
a.rangReg,
    case when b.rangRegTo is null then 0 
         else b.rangRegTo 
    end as rangRegTo,
    min(a.rangVal) as rangVal, max(a.rangVal) + 1 as rangValTo
from head a left join tail b on a.product_id = b.product_id 
    and a.state > 0
    and a.rangVal = b.rangVal and a.rangRegRang = b.rangRegToRang
group by a.product_id, a.state, a.rangReg, b.rangRegTo
union
select product_id, rangReg as state,
    rangReg, rangReg + 1 as rangRegTo,
    rangVal, 0 as rangValTo
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.rangReg   = b.rangReg
     left  join product c on a.product_id = c.product_id 
                          and a.rangRegTo = c.rangReg
     inner join product d on a.product_id = d.product_id 
                          and a.rangVal   = d.rangVal
     left  join product e on a.product_id = e.product_id 
                          and a.rangValTo = e.rangVal
     inner join product f on a.product_id = f.product_id 
                          and a.state     = f.rangReg;
Share this:

Like this:
