title: SQL - How to Find YTD amount?
body: <p>Let's say I have a table like this called hours:</p>
  
  <pre><code>employee, payperiod, hours
  greg, 1/31/2010, 12
  greg, 1/31/2010, 15
  mark, 1/31/2010, 10
  mark, 1/31/2010, 11
  greg, 2/28/2010, 12
  greg, 2/28/2010, 15
  greg, 2/28/2010, 4
  </code></pre>
  
  <p>How would I find the year to date hours for a given employee and payperiod?</p>
  
  <p>For the above example I'd want to get to:</p>
  
  <pre><code>employee, payperiod, hours_YTD
  greg, 1/31/2010, 27
  mark, 1/31/2010, 21
  greg, 2/28/2010, 58
  mark, 2/28/2010, 21  (this row is a nice to have, I can live without it if it's too tricky)
  </code></pre>
  
  <p>So far I've tried this but the total seems to be off:</p>
  
  <pre><code>select employee,payperiod,sum(hours) from hours h1
   join hours h2 on h2.payperiod&lt;=h1.payperiod and h2.payperiod&gt;=1/1/2010
    and h1.employee=h2.employee
   group by h1.employee, h1.payperiod
  </code></pre>
  
  <p>(I'm in SQL Server 2005 if it matters)</p>