title: MS SQL distinct results based on other table
body: <p>I want to generate a new table that shows the first and last entry of another table based on its primary key.</p>
  
  <p>I have a Meter Table that contains all the <code>Meter Ids</code></p>
  
  <p><strong>Meter Table</strong></p>
  
  <pre><code>MeterId CostPerUnit
  1    1.23
  2    4.14
  3    5.31
  4    13.13
  </code></pre>
  
  <p>Then I have a Measurements table that does half hourly records like</p>
  
  <p><strong>Measurement Table</strong></p>
  
  <pre><code>MeterId    StartTime    NumberOfUnits
  1    2002-12-01 00:01:00    25
  1    2002-12-01 00:30:00    78
  1    2003-09-01 02:30:00    30
  1    2008-07-02 01:00:00    15
  2    1999-01-01 00:30:00    23
  2    2000-03-23 05:03:00    30
  2    2008-04-18 18:30:00    25
  3    2006-04-03 12:00:00    30
  4    2004-04-04 11:30:00    13
  4    2008-09-09 15:00:00    15
  </code></pre>
  
  <p>The results table should be like this:</p>
  
  <pre><code>MeterId    FirstAvailability    LastAvailability
  1    2002-12-01    2008-07-02
  2    1999-01-01    2008-04-18
  3    2006-04-03    2006-04-03
  4    2004-04-04    2008-09-09
  </code></pre>
  
  <p>So:</p>
  
  <ol>
  <li>Date must be unique by day (I figured that I could use <code>SELECT DISTINCT CAST(StartTime AS DATE)</code> ) </li>
  <li>Find the first and last entry (I figured that I could use <code>ORDER BY ASC</code> and <code>ORDER BY DESC</code> on for a particular MeterId, but not sure how to iterate all meter ids)</li>
  </ol>
  
  <p>NOTE:
  - The measurement table consists of 10 million rows with 8000 meter ids.</p>