title: Find out which raw material made a manufactured product
body: <p>I've being trying to solve this for two days now. I work with manufacturing control. A client has asked me to calculate the raw material consumption for their products. The data is stored in two table, <em>item</em> and <em>material</em>,
  in the former table is stored the begin and end time for manufacturing the item, in the latter table is stored when the raw material was put in the machine and 
  which lot was used.</p>
  
  <p>The manufacturing process is like this: the operator adds the 
  raw materials to the machine, input the time at a terminal. Some time later he starts the machine, and it fuses kilometers of plastic films, each item is a roll of that film that usually weight hundreds of kilos. The machine automatically insert the begin and end time of each roll in the <em>item</em> table. </p>
  
  <p>When the raw material is about to be expended, the operator stops the machine and insert more raw materials from a different lot, and then starts the machine again. </p>
  
  <p>Depending on the size of the lot, it can last several rolls, or a single roll can be made from different lots of materials, </p>
  
  <p><em>Item table</em></p>
  
  <pre><code>| begin_time  | end_time  | item |
  |-------------|-----------|------|
  |   8:49      | 9:29      |   1  |
  |   9:29      | 10:04     |   2  |
  |  10:04      | 10:42     |   3  |
  </code></pre>
  
  <p><em>Material Table</em></p>
  
  <pre><code>| begin_time | material |
  |------------|----------|
  | 7:21       | A1       |
  | 7:21       | A2       |
  | 9:39       | B1       |
  | 9:39       | B2       |
  | 9:50       | C1       |
  | 9:51       | C2       |
  |10:14       | D1       |   
  |10:14       | D2       |
  </code></pre>
  
  <p>As it can be see the first item was done with material A1 and A2, but the second item started with A1,A2 then changed to B1,B2 and finished
  with C1 and C2
  I'm having problems trying to relate which raw material lot was used in which item. </p>
  
  <p>I have tried several variation on queries like this</p>
  
  <pre><code>select p.item,i.material
  from product p
  join (
        select distinct a.begin_time as dt1, b.begin_time  as dt2
        from material a 
        join material b on a.begin_time &lt; b.begin_time
  ) i on i.dt1 &gt; p.begin_time and i.dt2 &lt;= p.end_time
  where p.item = 2
  </code></pre>
  
  <p>The result for that query would be something like that</p>
  
  <pre><code>|  item  |  material |
  |--------|-----------|
  |    1   |    A1     |
  |    1   |    A2     |
  |    2   |    A1     |
  |    2   |    A2     |
  |    2   |    B1     |
  |    2   |    B2     |
  |    2   |    C1     |
  |    2   |    C2     |
  |    3   |    C1     |
  |    3   |    C2     |
  |    3   |    D1     |
  |    3   |    D2     |
  </code></pre>