title: Oracle SQL - Aggregate Elapsed Times within a particular range
body: <p>Essentially I want to calculate, for a given item; which is opened, remains open, and then is closed; the amount of time which this item was in a particular state. The state information is contained in a table, while the open / close times are held in another. Visually represented below</p>
  
  <pre><code>      OPEN_TS [------------------------------------------] CLOSE_TS
     X1[----------] X2[-----] X1[----------] X2[----] X1[-----]
  
  desired:
                --]             [----------]            [---
  </code></pre>
  
  <p>Working from the above diagram, I want to aggregate the amount of time this item spent in the state "X1" ONLY when the item was also OPEN. Basically find all the sections item A was OPEN and also in state X and aggregate all those time-stamps to get an total elapsed time (eg. 40 seconds spent open and in state X1)</p>
  
  <p>Edit: attempt at a better diagram in excel. <a href="https://i.stack.imgur.com/gjNXE.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/gjNXE.png" alt="enter image description here"></a></p>
  
  <p>Edit 2: Tabular Representation of Data, Interested in aggregating amount of time spent in state 1 which falls between the open and close TS of the item with id 101
  <a href="https://i.stack.imgur.com/Sjl8i.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Sjl8i.png" alt="enter image description here"></a></p>