title: SQL (bigquery) - SUM continues values with start/stop DATETIME within time interval
body: <p>Given the following <em>Table 1</em> that represents continues drug administration in a steady rate:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>row</th>
  <th>id</th>
  <th>starttime</th>
  <th>endtime</th>
  <th>drug_total_amount</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>21</td>
  <td>2163-04-13T03:00:00</td>
  <td>2163-04-13T06:00:00</td>
  <td>99</td>
  </tr>
  <tr>
  <td>2</td>
  <td>21</td>
  <td>2163-04-13T16:00:00</td>
  <td>2163-04-13T23:00:00</td>
  <td>540</td>
  </tr>
  <tr>
  <td>3</td>
  <td>21</td>
  <td>2163-04-14T02:30:00</td>
  <td>2163-04-14T17:00:00</td>
  <td>308</td>
  </tr>
  <tr>
  <td>4</td>
  <td>21</td>
  <td>2163-04-14T17:00:00</td>
  <td>2163-04-14T20:30:00</td>
  <td>72</td>
  </tr>
  <tr>
  <td>5</td>
  <td>21</td>
  <td>2163-04-14T20:40:00</td>
  <td>2163-04-15T00:00:00</td>
  <td>40</td>
  </tr>
  <tr>
  <td>6</td>
  <td>6</td>
  <td>2163-04-14T17:00:00</td>
  <td>2163-04-14T20:30:00</td>
  <td>72</td>
  </tr>
  <tr>
  <td>7</td>
  <td>6</td>
  <td>2163-04-14T20:40:00</td>
  <td>2163-04-15T00:00:00</td>
  <td>39.75</td>
  </tr>
  <tr>
  <td>8</td>
  <td>6</td>
  <td>2163-04-15T00:00:00</td>
  <td>2163-04-15T01:00:00</td>
  <td>13.5</td>
  </tr>
  <tr>
  <td>9</td>
  <td>6</td>
  <td>2163-04-15T01:00:00</td>
  <td>2163-04-15T02:00:00</td>
  <td>9</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>And <em>Table 2</em> that represents events with timestamps:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>row</th>
  <th>id</th>
  <th>event</th>
  <th>event_time</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>21</td>
  <td>1</td>
  <td>2163-04-14T17:30:00</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p><strong>What is the most efficient way to sum administrated values to a specific id in a given time interval prior to the event?</strong></p>
  <hr />
  <p><strong>For example:</strong>
  I want to sum a drug amount for a 24 hour interval before event id 1 (<code>pre_24_hours_drug</code>) -
  The relevant rows in table 1 are 2-4 (per id and time range):</p>
  <ol start="2">
  <li>Only the partial amount between 17:00 and <code>endtime</code> (~424)</li>
  <li>Whole amount (308)</li>
  <li>Only the partial amount between <code>starttime</code> and 17:00 (~10)</li>
  </ol>
  <p>the result should be:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>row</th>
  <th>id</th>
  <th>event</th>
  <th>event_time</th>
  <th>pre_24_hours_drug</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>21</td>
  <td>1</td>
  <td>2163-04-14T17:30:00</td>
  <td>742.5714286</td>
  </tr>
  </tbody>
  </table>
  </div>