SQL - 如何查找年初至今的金额？

Question

假设我有一个名为 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

我如何找到给定员工和付薪期的年初至今工时？

对于上面的例子，我想:

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)

到目前为止，我已经试过了，但总数似乎不对:

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

(如果重要的话，我在 SQL Server 2005 中)

Answer 1

这将使用 SELECT 中的子查询获取运行总计。

正在努力在 28 号获得 Mark。

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