title: SQL query to get status as of a given date
body: <p>I'm sure this has been answered before but couldn't find it.</p>
  
  <p>I have a table of items which change status every few weeks.  I want to look at an arbitrary day and figure out how many items were in each status.</p>
  
  <p>For example:</p>
  
  <p><strong>tbl_ItemHistory</strong></p>
  
  <pre><code>ItemID
  StatusChangeDate
  StatusID
  </code></pre>
  
  <p>Sample data:</p>
  
  <pre><code>1001, 1/1/2010, 1
  1001, 4/5/2010, 2
  1001, 6/15/2010, 4
  1002, 4/1/2010, 1
  1002, 6/1/2010, 3
  ...
  </code></pre>
  
  <p>So I need to figure out how many items were in each status for a given day.  So on 5/1/2010, there was one item (1001) in status 2 and one item in status 1 (1002).</p>
  
  <p>Since these items don't change status very often, maybe I could create a cached table every night that has a row for every item and every day of the year?  I'm not sure if that's best or how to do that though</p>
  
  <p>I'm using SQL Server 2008R2</p>