我有这个通用表表达式
WITH total_hour
AS (
SELECT
employee_id,
SUM(ROUND(CAST(DATEDIFF(MINUTE, start_time, finish_time) AS NUMERIC(18, 0)) / 60, 2)) AS total_h
FROM Timesheet t
WHERE t.employee_id = @employee_id
AND DENSE_RANK() OVER (
ORDER BY DATEDIFF(DAY, '20130925', date_worked) / 7 DESC ) = @rank
GROUP BY t.personnel_id
)
这是示例数据:
ID employee_id worked_date start_time finish_time
1 1 2013-09-25 09:00:00 17:30:00
2 1 2013-09-26 07:00:00 17:00:00
8 1 2013-10-01 09:00:00 17:00:00
9 1 2013-10-04 09:00:00 17:00:00
12 1 2013-10-07 09:00:00 17:00:00
13 1 2013-10-30 09:00:00 17:00:00
14 1 2013-10-28 09:00:00 17:00:00
15 1 2013-11-01 09:00:00 17:00:00
假设星期三是一周的第一天,我的基础日期是2013-09-25。我想获得@rank为1时从09-25到10-01的总工作小时数,以及@ rank = 2时从10-02到10-08的总小时数,依此类推。
谢谢
要获取员工在特定一周内的工作时间,只需使用适当的WHERE标准即可。无需为此使用DENSE_RANK或类似的窗口函数。
假设您有一个@Week参数,该参数包含一个整数(当前周为0,上周为1,其前一周为2,依此类推):
SELECT
employee_id
SUM(ROUND(CAST(DATEDIFF(MINUTE, start_time, finish_time) AS NUMERIC(18, 0)) / 60, 2)) AS total_h
FROM
Timesheet t
WHERE
t.employee_id = @employee_id AND
date_worked BETWEEN DATEADD(ww, DATEDIFF(ww,0,GETDATE()) - @Week, 0)
AND DATEADD(ww, DATEDIFF(ww,0,GETDATE()) - @Week, 0) + 7
在这里,我将当前日期(GETDATE()
)用作基准日期,但是20130925
如果需要,您可以将其替换为。
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句