title: SQL server select one record per day based on value in field
body: <p>I have a followup question to this question.</p>
  
  <p><a href="https://stackoverflow.com/questions/6527527/sql-server-get-values-of-top-three-records-and-display-in-one-row-per-person/6535383#6535383">SQL Server Get values of top three records and display in one row per person</a></p>
  
  <pre><code>ID   Name       Date1                   Value2  Date2                   Value2  Date3                   Value3  Date4                   Value4  Date5                   Value5  Date6                   Value6  Date7                   Value7
  
  12  John Smith  2011-06-27 14:06:10.517 None    2011-06-27 00:17:53.987 None    2011-06-26 21:56:07.577 Medium  2011-06-26 13:32:31.190 None    2011-06-26 02:47:54.357 None    2011-06-25 19:32:00.000 Medium  2011-06-25 13:43:22.000 Medium
  12  Jack Smith  2011-06-27 05:54:59.320 None    2011-06-26 06:28:55.033 None    2011-06-25 16:25:00.000 Medium  2011-06-25 14:27:11.017 Large   2011-06-25 06:11:45.793 Large   2011-06-24 19:33:24.520 Medium  2011-06-24 06:17:35.887 None
  </code></pre>
  
  <p>I need to get one value per day.  If there is a value that is not equal to 'None', I need that record for the given date.  </p>
  
  <p>Here is what the outcome should look like:</p>
  
  <pre><code>ID   Name       Date1                   Value2  Date2                   Value2  Date3                   Value3  Date4                   Value4  Date5                   Value5  Date6                   Value6  Date7                   Value7
  
  12  John Smith  2011-06-27 00:17:53.987 None    2011-06-26 21:56:07.577 Medium  2011-06-25 13:43:22.000 Medium
  12  Jack Smith  2011-06-27 05:54:59.320 None    2011-06-26 06:28:55.033 None    2011-06-25 06:11:45.793 Large   2011-06-24 19:33:24.520 Medium  
  </code></pre>
  
  <p>My raw data is in this format:
  (Records I need are marked with *)</p>
  
  <pre><code>ID  Name        Date                    Value
  12  JACK Smith  2011-06-27 05:54:59.320 None    *
  12  JACK Smith  2011-06-26 06:28:55.033 None    *
  12  JACK Smith  2011-06-25 16:25:00.000 Medium  
  12  JACK Smith  2011-06-25 14:27:11.017 Large
  12  JACK Smith  2011-06-25 06:11:45.793 Large   *
  12  JACK Smith  2011-06-24 19:33:24.520 Medium  *
  12  JACK Smith  2011-06-24 06:17:35.887 None
  12  JACK Smith  2011-06-23 00:30:28.363 None    *
  12  JACK Smith  2011-06-22 00:47:41.800 None    *
  12  JACK Smith  2011-06-21 06:03:55.000 None    *
  </code></pre>
  
  <p>Any help is greatly appreciated.</p>