如何使用sql server 2008获取特定月份员工的总工作时间？ [英] How can I get the total worked hours by employee in a specific month using sql server 2008?

问题描述

我正在尝试显示每个员工在特定月份的总工作时间，我正在使用一个表Emp_Attendance，其中包含（EmpID，_date Times_In，Times_Out）



我尝试这个sql查询，但它给了我一个错误的结果（正确的结果是14:00，但它给了我104：16）

I'm trying to display the total worked hours for each employee in a specific month, I'm using a table "Emp_Attendance" which contain the (EmpID,_date Times_In, Times_Out)

I try this sql query but it gives me a wrong result (the right result is 14:00 but it gives me 104:16)

WITH att As
( SELECT EmpID ,
Times_In, Times_Out,
ROW_NUMBER() OVER (PARTITION BY EmpID ORDER BY Times_In) As RNI,
ROW_NUMBER() OVER (PARTITION BY EmpID ORDER BY Times_Out DESC) As RNO
FROM dbo.Emp_Attendance
WHERE
Month(Times_Out) = 6  )

SELECT EmpID,
CONVERT(varchar(3),(Datediff (MINUTE,  Max(CASE WHEN RNI = 1 THEN Times_In ELSE Null END), Max(CASE WHEN RNO = 4 THEN Times_Out ELSE Null END))+
   Datediff (MINUTE,   Max(CASE WHEN RNI = 2 THEN Times_In ELSE Null END), Max(CASE WHEN RNO = 3 THEN Times_Out ELSE Null END))+
    Datediff (MINUTE,   Max(CASE WHEN RNI = 3 THEN Times_In ELSE Null END), Max(CASE WHEN RNO = 2 THEN Times_Out ELSE Null END))+
     Datediff (MINUTE,    Max(CASE WHEN RNI = 4 THEN Times_In ELSE Null END), Max(CASE WHEN RNO = 1 THEN Times_Out ELSE Null END)) ) /60 )
     +':' +  RIGHT('0' + CONVERT(varchar(2),
    (Datediff (MINUTE,  Max(CASE WHEN RNI = 1 THEN Times_In ELSE Null END), Max(CASE WHEN RNO = 4 THEN Times_Out ELSE Null END))+
   Datediff (MINUTE,   Max(CASE WHEN RNI = 2 THEN Times_In ELSE Null END), Max(CASE WHEN RNO = 3 THEN Times_Out ELSE Null END))+
    Datediff (MINUTE,   Max(CASE WHEN RNI = 3 THEN Times_In ELSE Null END), Max(CASE WHEN RNO = 2 THEN Times_Out ELSE Null END))+
     Datediff (MINUTE,    Max(CASE WHEN RNI = 4 THEN Times_In ELSE Null END), Max(CASE WHEN RNO = 1 THEN Times_Out ELSE Null END))) %60 ) ,2)
     as total
FROM att
GROUP BY EmpID

推荐答案

目前还不完全清楚为什么要检查行号以及为什么你有一个CTE。



从您描述的问题来看，这个简单的查询将为您提供所需的结果

It's not entirely clear why you are checking Row Numbers nor why you have a CTE.

From the problem as you have described it, this simple query will give you the results you need

select EmpId, SUM(DATEDIFF(MINUTE, Times_In, Times_Out)) / 60.0
from Emp_Attendance
where MONTH(Times_In) = 6
GROUP BY EmpId

请注意该部门的 .0 ，以确保我们不会失去任何部分时间。

Note the .0 on the division to ensure we don't lose any partial hours.

这篇关于如何使用sql server 2008获取特定月份员工的总工作时间？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！