title: Loading multiple time series simultaneously using SQL
body: <p>Suppose I have this exact dataset:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>date</th>
  <th>widget ID</th>
  <th>widget price</th>
  <th>widget expiry date</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>2020-01-01</td>
  <td>A</td>
  <td>1</td>
  <td>2020-03-01</td>
  </tr>
  <tr>
  <td>2020-01-01</td>
  <td>B</td>
  <td>2</td>
  <td>2020-04-01</td>
  </tr>
  <tr>
  <td>2020-01-01</td>
  <td>C</td>
  <td>3</td>
  <td>2020-05-01</td>
  </tr>
  <tr>
  <td>2020-01-01</td>
  <td>D</td>
  <td>4</td>
  <td>2020-06-01</td>
  </tr>
  <tr>
  <td>2020-01-02</td>
  <td>A</td>
  <td>1.1</td>
  <td>2020-03-01</td>
  </tr>
  <tr>
  <td>2020-01-02</td>
  <td>B</td>
  <td>2.05</td>
  <td>2020-04-01</td>
  </tr>
  <tr>
  <td>2020-01-02</td>
  <td>C</td>
  <td>3.7</td>
  <td>2020-05-01</td>
  </tr>
  <tr>
  <td>2020-01-02</td>
  <td>D</td>
  <td>3.8</td>
  <td>2020-06-01</td>
  </tr>
  <tr>
  <td>2020-01-03</td>
  <td>A</td>
  <td>1.15</td>
  <td>2020-03-01</td>
  </tr>
  <tr>
  <td>2020-01-03</td>
  <td>B</td>
  <td>2.09</td>
  <td>2020-04-01</td>
  </tr>
  <tr>
  <td>2020-01-03</td>
  <td>C</td>
  <td>3.54</td>
  <td>2020-05-01</td>
  </tr>
  <tr>
  <td>2020-01-03</td>
  <td>D</td>
  <td>4.2</td>
  <td>2020-06-01</td>
  </tr>
  <tr>
  <td>2020-01-04</td>
  <td>A</td>
  <td>1.19</td>
  <td>2020-03-01</td>
  </tr>
  <tr>
  <td>2020-01-04</td>
  <td>B</td>
  <td>2.14</td>
  <td>2020-04-01</td>
  </tr>
  <tr>
  <td>2020-01-04</td>
  <td>C</td>
  <td>3.73</td>
  <td>2020-05-01</td>
  </tr>
  <tr>
  <td>2020-01-04</td>
  <td>D</td>
  <td>4.30</td>
  <td>2020-06-01</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Say I wanted to simultaneously retrieve the full time series of the two following widgets using a single SQL query:</p>
  <ul>
  <li>the widget which on date 2020-01-01 had price as close as possible to 1 and expiry date as close as possible to 2020-03-10.</li>
  <li>the widget which on date 2020-01-03 had price as close as possible to 3.5 and expiry date as close as possible to 2020-05-15.</li>
  </ul>
  <p>In other words, this exact table:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>date</th>
  <th>widget ID</th>
  <th>widget price</th>
  <th>widget expiry date</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>2020-01-01</td>
  <td>A</td>
  <td>1</td>
  <td>2020-03-01</td>
  </tr>
  <tr>
  <td>2020-01-01</td>
  <td>C</td>
  <td>3</td>
  <td>2020-05-01</td>
  </tr>
  <tr>
  <td>2020-01-02</td>
  <td>A</td>
  <td>1.1</td>
  <td>2020-03-01</td>
  </tr>
  <tr>
  <td>2020-01-02</td>
  <td>C</td>
  <td>3.7</td>
  <td>2020-05-01</td>
  </tr>
  <tr>
  <td>2020-01-03</td>
  <td>A</td>
  <td>1.15</td>
  <td>2020-03-01</td>
  </tr>
  <tr>
  <td>2020-01-03</td>
  <td>C</td>
  <td>3.54</td>
  <td>2020-05-01</td>
  </tr>
  <tr>
  <td>2020-01-04</td>
  <td>A</td>
  <td>1.19</td>
  <td>2020-03-01</td>
  </tr>
  <tr>
  <td>2020-01-04</td>
  <td>C</td>
  <td>3.73</td>
  <td>2020-05-01</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>How would you recommend going about it?</p>
  <p>Generalising this example, suppose you had a list of tuples like below, where price_i is a target price and expiry_date_i is a target expiry date.</p>
  <p>(date_1, price_1, expiry_date_1), (date_2, price_2, expiry_date_2),
  (date_3, price_3, expiry_date_3),...</p>
  <p>How would you load all of the corresponding widgets' time series in one go?</p>
  <p>For the time being I am retrieving these widgets' IDs separately using a SQL query like this one (in this example date='2020-01-01', price=1, expiry date='2020-03-10'). Then collecting all of these retrieved IDs I load the full widget time series.</p>
  <pre><code>WITH sample AS 
  (SELECT *, ABS(DATEDIFF(day,widget_expiry_date, '2020-03-10')) AS date_diff, ABS(widget_price - 1) As price_diff 
  FROM data WHERE date='2020-01-01'
  ORDER BY date_diff ASC, price_diff ASC)
  SELECT TOP 1 widget_ID FROM sample
  </code></pre>
  <p>As you can imagine this is extremely inefficient. I wonder if there is a smarter way about it?</p>
  <p>Thank you for your time and apologies in advance for the noobish question.</p>