Oracle SQL
  • LICENSE

SQL: Split periods

Posted on June 2, 2019 by Sayan Malakshinov Posted in oracle, SQL

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'
);
intervals match_recognize sql
« Workarounds for JPPD with view and table(kokbf$), xmltable or json_table functions
flashback_transaction_query: fixed table full table scan »
photo Sayan Malakshinov

Oracle ACE Pro Oracle ACE Pro

DEVVYOracle Database Developer Choice Award winner

Oracle performance tuning expert

UK / Cambridge

LinkedIn   Twitter
sayan@orasql.org

Recent Posts

  • CBO and Partial indexing
  • Slow index access “COL=:N” where :N is NULL
  • Where does the commit or rollback happen in PL/SQL code?
  • :1 and SP2-0553: Illegal variable name “1”.
  • ORA exceptions that can’t be caught by exception handler

Recent Comments

  • Oracle SGA 값을 증가 시킬 때 발생 장애 원인 – DBA의 정석 on Example of controlling “direct path reads” decision through SQL profile hints (index_stats/table_stats)
  • Oracle SQL | Oracle diagnostic events — Cheat sheet on Where does the commit or rollback happen in PL/SQL code?
  • Functions & Subqueries | Oracle Scratchpad on Deterministic function vs scalar subquery caching. Part 3
  • Materialized views state turns into compilation_error after refresh - kranar.top - Answering users questions... on Friday prank: select from join join join
  • Exadata Catalogue | Oracle Scratchpad on When bloggers get it wrong – part 1
  • Exadata Catalogue | Oracle Scratchpad on Serial Scans failing to offload
  • lateral join – decorrelation gone wrong – svenweller on Lateral view decorrelation(VW_DCL) causes wrong results with rownum
  • 255 column catalogue | Oracle Scratchpad on Intra-block row chaining optimization in 12.2
  • 255 column catalogue | Oracle Scratchpad on row pieces, 255 columns, intra-block row chaining in details
  • opt_estimate catalogue | Oracle Scratchpad on Correct syntax for the table_stats hint

Blogroll

  • Alex Fatkulin
  • Alexander Anokhin
  • Andrey Nikolaev
  • Charles Hooper
  • Christian Antognini
  • Coskan Gundogar
  • David Fitzjarrell
  • Igor Usoltsev
  • Jonathan Lewis
  • Karl Arao
  • Mark Bobak
  • Martin Bach
  • Martin Berger
  • Neil Chandler
  • Randolf Geist
  • Richard Foote
  • Riyaj Shamsudeen
  • Tanel Poder
  • Timur Akhmadeev
  • Valentin Nikotin

Meta

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org
©Sayan Malakshinov. Oracle SQL