SQL - How to Find YTD amount?

Let's say I have a table like this called hours:

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

How would I find the year to date hours for a given employee and payperiod?

For the above example I'd want to get to:

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)

So far I've tried this but the total seems to be off:

select employee,payperiod,sum(hours) from hours h1
 join hours h2 on h2.payperiod<=h1.payperiod and h2.payperiod>=1开发者_高级运维/1/2010
  and h1.employee=h2.employee
 group by h1.employee, h1.payperiod

(I'm in SQL Server 2005 if it matters)

This will get the running total, using a subquery in the SELECT.

Working on getting Mark on 28th.

select employee, 
        payperiod,
        (SELECT sum ([hours]) FROM @hours f2 
             WHERE f2.payperiod <= f.payperiod and f2.employee=f.employee) as hh

 from @hours as f
 where  YEAR(payperiod) = year(getdate()) --current year.
 group by employee, payperiod
 order by payperiod

There is probably a better way to get the first of the year, but this seemed a little easier.

Select 
    H.employee
  , H.payperiod
  , (Select Sum(H1.hours) 
     From hours as H1         
     Where H1.employee = H.employee 
        and H1.payperiod <= H.payperiod
    ) as hours_YTD
from hours as H
where Year(H.payperiod) = Year(GetDate()
  and H.payperiod <= GetDate()
group by employee
   , payperiod

*the last row will require the practive of an entirely new religion unless it is in the table or there is something you are not telling us.