title: Best approach to time-series data in SQL Server 2008
body: <p>Via a <code>stored procedure</code> in <code>SQL Server 2008</code>, I am trying to show the number of transactions from the past 7 days, and then compare those numbers to a 7 day period a year ago.  Additionally, I'd like to be able to show month-to-date versus month-to-date a year ago.  Or this month to last month.  Etc.  Essentially, <code>time-series</code> data.  </p>
  
  <p>I've got a table with all the individual transactions.  The two options I can think of are:</p>
  
  <ol>
  <li><p>Calculate each individual day or month and return the result set
  at run time.  </p></li>
  <li><p>Calculate each invididual day and put the result
  into a table.  Update that daily.  Create another table for weeks. 
  Another table for months.  Each day, add a record or update the
  existing record.  That certainly will make the query faster, since
  the data is already calculated.  However, if I had to make updates to previous days (taking into account returns/cancels) </p></li>
  </ol>
  
  <p>Note:  This is an internal application, so it won't see thousands of requests a minute.  </p>
  
  <p>What is the best way to return this type of data?  </p>
  
  <p>The other alternative I've thought about is using <code>HBase OpenTSDB</code> via Option 2, but seems like it's the same process.  </p>