Some time ago there was a question on the sql.ru about splitting validity periods for gym cards to enabled and blocked periods:
-- we have a table with gym cards (card id, validity period: from/to)
CREATE TABLE cards(id_card NUMBER, num_card VARCHAR2(100), dt_from DATE, dt_to DATE);
-- periods when cards were blocked (record id, card id, dates from/to)
CREATE TABLE blocks(id_block NUMBER, id_card NUMBER, dt_from DATE, dt_to DATE);
-- cards data:
INSERT INTO cards (id_card, num_card, dt_from, dt_to) VALUES (1,'111111', TO_DATE('01.09.2016','DD.MM.YYYY'), TO_DATE('01.09.2019','DD.MM.YYYY'));
INSERT INTO cards (id_card, num_card, dt_from, dt_to) VALUES (2,'222222', TO_DATE('06.10.2017','DD.MM.YYYY'), TO_DATE('31.12.2050','DD.MM.YYYY'));
INSERT INTO cards (id_card, num_card, dt_from, dt_to) VALUES (3,'333333', TO_DATE('09.05.2019','DD.MM.YYYY'), TO_DATE('31.12.2050','DD.MM.YYYY'));
-- blocks data:
INSERT INTO blocks (id_block, id_card, dt_from, dt_to) VALUES (101, 1, TO_DATE('20.03.2017','DD.MM.YYYY'), TO_DATE('09.04.2017','DD.MM.YYYY'));
INSERT INTO blocks (id_block, id_card, dt_from, dt_to) VALUES (102, 1, TO_DATE('14.01.2018','DD.MM.YYYY'), TO_DATE('03.02.2018','DD.MM.YYYY'));
INSERT INTO blocks (id_block, id_card, dt_from, dt_to) VALUES (103, 1, TO_DATE('18.02.2019','DD.MM.YYYY'), TO_DATE('10.03.2019','DD.MM.YYYY'));
INSERT INTO blocks (id_block, id_card, dt_from, dt_to) VALUES (201, 2, TO_DATE('02.08.2018','DD.MM.YYYY'), TO_DATE('10.11.2018','DD.MM.YYYY'));
COMMIT;
Just for fun, I’ve solved it using match_recognise:
with
cards (id_card, num_card, dt_from, dt_to) as (
select 1,'111111', TO_DATE('01.09.2016','DD.MM.YYYY'), TO_DATE('01.09.2019','DD.MM.YYYY') from dual union all
select 2,'222222', TO_DATE('06.10.2017','DD.MM.YYYY'), TO_DATE('31.12.2050','DD.MM.YYYY') from dual union all
select 3,'333333', TO_DATE('09.05.2019','DD.MM.YYYY'), TO_DATE('31.12.2050','DD.MM.YYYY') from dual
),blocks (id_block, id_card, dt_from, dt_to) as (
select 101, 1, TO_DATE('20.03.2017','DD.MM.YYYY'), TO_DATE('09.04.2017','DD.MM.YYYY') from dual union all
select 102, 1, TO_DATE('14.01.2018','DD.MM.YYYY'), TO_DATE('03.02.2018','DD.MM.YYYY') from dual union all
select 103, 1, TO_DATE('18.02.2019','DD.MM.YYYY'), TO_DATE('10.03.2019','DD.MM.YYYY') from dual union all
select 201, 2, TO_DATE('02.08.2018','DD.MM.YYYY'), TO_DATE('10.11.2018','DD.MM.YYYY') from dual
)
,data as (
select *
from (
select *
from (
select id_card,dt_from,dt_to,'c' t from cards
union all
select id_card,dt_to,dt_from,'b' from blocks
) v
--where v.id_card=1 -- here we can filter card ID if needed
) piv
unpivot(dt for x in (dt_from as 'dt_from', dt_to as 'dt_to'))
)
select *
from data
match_recognize (
partition by id_card
order by dt /* we can modify add "t" into "order by clause" to make it more deterministic */
measures
match_number() as N,
first(dt) as dt_from,
last(dt) as dt_to
pattern (x_start+ x_end)
define
x_start as x = 'dt_from'
,x_end as x = 'dt_to'
);