title: find overlap between two sets of date ranges
body: <p>I would like to find the overlap between two sets of date ranges for every order number using Oracle SQL or PL/SQL.</p>
  <p>The inputs are &quot;result set one&quot; and &quot;result set two&quot;. The output should be &quot;overlap&quot;.</p>
  <p><strong>result set one</strong></p>
  <pre><code>WITH T_RESULT_SET_ONE as(
  select 21365 order_number,to_date('01/01/2021 09:00:00', 'DD/MM/YYYY HH24:MI:SS') start_date_time, to_date('01/01/2021 10:30:00', 'DD/MM/YYYY HH24:MI:SS') finish_date_time FROM DUAL
  UNION
  select 21365 order_number,to_date('02/01/2021 14:00:00', 'DD/MM/YYYY HH24:MI:SS') start_date_time, to_date('02/01/2021 18:00:00', 'DD/MM/YYYY HH24:MI:SS') finish_date_time FROM DUAL
  UNION
  select 21367 order_number,to_date('01/01/2021 08:00:00', 'DD/MM/YYYY HH24:MI:SS') start_date_time, to_date('01/01/2021 09:43:00', 'DD/MM/YYYY HH24:MI:SS') finish_date_time FROM DUAL
  UNION
  select 21367 order_number,to_date('01/01/2021 16:34:00', 'DD/MM/YYYY HH24:MI:SS') start_date_time, to_date('01/01/2021 18:15:00', 'DD/MM/YYYY HH24:MI:SS') finish_date_time FROM DUAL
  union
  select 21367 order_number,to_date('04/01/2021 15:00:00', 'DD/MM/YYYY HH24:MI:SS') start_date_time, to_date('04/01/2021 16:15:00', 'DD/MM/YYYY HH24:MI:SS') finish_date_time FROM DUAL
  )
  </code></pre>
  <p>or</p>
  <p><a href="https://i.stack.imgur.com/CgDWK.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/CgDWK.png" alt="enter image description here" /></a></p>
  <p><strong>result set two</strong></p>
  <pre><code>T_RESULT_SET_TWO as(
  select 21365 order_number,to_date('01/01/2021 09:30:00', 'DD/MM/YYYY HH24:MI:SS') start_date_time, to_date('01/01/2021 09:45:00', 'DD/MM/YYYY HH24:MI:SS') finish_date_time FROM DUAL
  UNION
  select 21365 order_number,to_date('02/01/2021 13:00:00', 'DD/MM/YYYY HH24:MI:SS') start_date_time, to_date('02/01/2021 17:00:00', 'DD/MM/YYYY HH24:MI:SS') finish_date_time FROM DUAL
  union
  select 21367 order_number,to_date('01/01/2021 09:00:00', 'DD/MM/YYYY HH24:MI:SS') start_date_time, to_date('01/01/2021 10:00:00', 'DD/MM/YYYY HH24:MI:SS') finish_date_time FROM DUAL
  UNION
  select 21367 order_number,to_date('01/01/2021 16:00:00', 'DD/MM/YYYY HH24:MI:SS') start_date_time, to_date('01/01/2021 19:00:00', 'DD/MM/YYYY HH24:MI:SS') finish_date_time FROM DUAL
  UNION
  select 21367 order_number,to_date('05/01/2021 19:00:00', 'DD/MM/YYYY HH24:MI:SS') start_date_time, to_date('04/01/2021 19:46:00', 'DD/MM/YYYY HH24:MI:SS') finish_date_time FROM DUAL
  )
  </code></pre>
  <p>or</p>
  <p><a href="https://i.stack.imgur.com/AK0Ba.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/AK0Ba.png" alt="enter image description here" /></a></p>
  <p><strong>overlap</strong></p>
  <pre><code>T_OVERLAP as
  (
  select 21365 order_number,to_date('01/01/2021 09:30:00', 'DD/MM/YYYY HH24:MI:SS') start_date_time, to_date('01/01/2021 09:45:00', 'DD/MM/YYYY HH24:MI:SS') finish_date_time FROM DUAL
  union
  select 21365 order_number,to_date('02/01/2021 14:00:00', 'DD/MM/YYYY HH24:MI:SS') start_date_time, to_date('02/01/2021 17:00:00', 'DD/MM/YYYY HH24:MI:SS') finish_date_time FROM DUAL
  union
  select 21367 order_number,to_date('01/01/2021 09:00:00', 'DD/MM/YYYY HH24:MI:SS') start_date_time, to_date('01/01/2021 09:43:00', 'DD/MM/YYYY HH24:MI:SS') finish_date_time FROM DUAL
  UNION
  select 21367 order_number,to_date('01/01/2021 16:34:00', 'DD/MM/YYYY HH24:MI:SS') start_date_time, to_date('01/01/2021 18:15:00', 'DD/MM/YYYY HH24:MI:SS') finish_date_time FROM DUAL
  )
  </code></pre>
  <p>or</p>
  <p><a href="https://i.stack.imgur.com/FREg2.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/FREg2.png" alt="enter image description here" /></a></p>
  <p>The following image ilustrates the operation I am trying to execute (the date ranges are not the same as the ones I provided earlier)</p>
  <p><a href="https://i.stack.imgur.com/jwNjT.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/jwNjT.png" alt="enter image description here" /></a></p>
  <p>Could anyone provide a SQL query or PL/SQL program that does that?</p>