title: Oracle SQL - How to create common date periods (using MATCH_RECOGNIZE?)
body: <p>I have problem with divide dates into correct periods.
  Here's an example:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>id</th>
  <th>count</th>
  <th>code</th>
  <th>date_from</th>
  <th>date_to</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>4364</td>
  <td>2</td>
  <td>201</td>
  <td>01/08/2022 15:00:00</td>
  <td>10/09/2022 22:00:00</td>
  </tr>
  <tr>
  <td>4364</td>
  <td>2</td>
  <td>201</td>
  <td>13/09/2022 05:20:00</td>
  <td>30/09/2022 17:00:00</td>
  </tr>
  <tr>
  <td>4364</td>
  <td>2</td>
  <td>4013</td>
  <td>29/08/2022 04:48:00</td>
  <td>19/11/2022 13:43:00</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>My goal is to get common periods for those dates, so in this case it will be:</p>
  <p>Expected result:</p>
  <pre><code>29/08/2022 15:00 - 10/09/2022 22:00
  
  13/09/2022 05:20 - 30/09/2022 17:00
  </code></pre>
  <p>Alternative cases:</p>
  <pre><code>IF
  201 - the same two rows
  4013 - 10/08/2022 - 08/09/2022
  THEN EXPECTED RESULT:
  10/08/2022 - 08/09/2022
  13/09/2022 - 30/09/2022
  
  IF
  201 - the same two rows
  4013 - 15/09/2022 - 22/09/2022
  THEN EXPECTED RESULT:
  01/08/2022 - 10/09/2022
  15/09/2022 - 22/09/2022
  
  IF
  201 - the same two rows
  4013 - 11/09/2022 - 12/09/2022
  THEN EXPECTED RESULT:
  NULL (zero rows)
  </code></pre>
  <p>The tricky part is that periods are already returned correctly per 'code' (that's why 201 has two periods), so I need to combine different codes somehow</p>
  <p>I was using something like this (but it doesn't work correctly if 'code 4013' is overlapping both dates of 201)</p>
  <pre><code> SELECT * 
     FROM table_gtt
   MATCH_RECOGNIZE (PARTITION BY id
                    ORDER     BY date_from, date_to
                    MEASURES
                        MIN(date_from)         date_from
                       ,MAX(date_to)           date_to
                    PATTERN (overlap* last_row)
                    DEFINE
                      overlap AS MAX(date_to) &gt;= NEXT(date_from)
                    ) 
  </code></pre>
  <p>Can anyone has idea how to solve this?</p>
  <p>Data:</p>
  <pre><code>CREATE GLOBAL TEMPORARY TABLE table_gtt
  ON COMMIT PRESERVE ROWS
  AS
  select 4364 id, 2 count, 201 code, TO_DATE('01/08/2022 15:00', 'DD/MM/YYYY HH24:MI') date_from, TO_DATE('10/09/2022 22:00', 'DD/MM/YYYY HH24:MI') date_to from dual
  union all
  select 4364 id, 2 count, 201 code, TO_DATE('13/09/2022 05:20', 'DD/MM/YYYY HH24:MI') date_from, TO_DATE('30/09/2022 17:00', 'DD/MM/YYYY HH24:MI') date_to from dual
  union all
  select 4364 id, 2 count, 4013 code, TO_DATE('29/08/2022 04:48', 'DD/MM/YYYY HH24:MI') date_from, TO_DATE('19/11/2022 13:43', 'DD/MM/YYYY HH24:MI') date_to from dual;
  </code></pre>