来自生物识别设备的考勤记录 [英] Attendance record from bio-metric device
本文介绍了来自生物识别设备的考勤记录的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
这是我的数据
|Id |EmpCode |CheckInCheckOutDate |WorkDate |InOutMode
|247 |51 |2017-02-13 20:08:52.000 |2017-02-13 20:08:52.000 |0
|392 |51 |2017-02-13 22:38:51.000 |2017-02-13 22:38:51.000 |1
|405 |51 |2017-02-13 22:59:18.000 |2017-02-13 22:59:18.000 |0
|415 |51 |2017-02-13 23:18:17.000 |2017-02-13 23:18:17.000 |1
|423 |51 |2017-02-13 23:33:44.000 |2017-02-13 23:33:44.000 |0
|456 |51 |2017-02-14 01:30:15.000 |2017-02-13 01:30:15.000 |1
|463 |51 |2017-02-14 02:52:02.000 |2017-02-13 02:52:02.000 |0
|483 |51 |2017-02-14 05:11:54.000 |2017-02-13 05:11:54.000 |1
|1034 |51 |2017-02-14 20:09:23.000 |2017-02-14 20:09:23.000 |0
|1172 |51 |2017-02-14 21:59:23.000 |2017-02-14 21:59:23.000 |1
|1217 |51 |2017-02-14 22:30:28.000 |2017-02-14 22:30:28.000 |0
|1238 |51 |2017-02-14 22:49:51.000 |2017-02-14 22:49:51.000 |1
|1257 |51 |2017-02-14 23:19:10.000 |2017-02-14 23:19:10.000 |0
|1315 |51 |2017-02-15 05:04:16.000 |2017-02-14 05:04:16.000 |1
|1323 |51 |2017-02-15 05:05:17.000 |2017-02-14 05:05:17.000 |0
|1329 |51 |2017-02-15 05:08:17.000 |2017-02-14 05:08:17.000 |1
|1330 |51 |2017-02-15 05:08:18.000 |2017-02-14 05:08:18.000 |1
我用过这个代码:
SELECT EmpCode,
CAST(I.CheckInCheckOutDate AS DATE) AS [Date],
CAST(I.WorkDate AS DATE) AS WorkDate,
CONVERT(VARCHAR(10), I.WorkDate, 108) AS CheckIn,
CONVERT(VARCHAR(10), O.WorkDate, 108) AS CheckOut,
CAST(DATEDIFF(second,I.WorkDate,O.WorkDate)/3600.0 AS DECIMAL(18,9)) [TotalHours]
FROM tblAttendance I
OUTER APPLY (
SELECT TOP 1 WorkDate,inOutMode FROM tblAttendance t
WHERE t.EmpCode = I.EmpCode AND t.WorkDate > I.WorkDate
ORDER BY t.WorkDate
) O
WHERE I.inOutMode = 0
AND O.inOutMode = 1 and I.empCode=51
代码结果为:
|EmpCode|Date| |WorkDate |CheckIn |CheckOut |TotalHours
|51 |2017-02-13 |2017-02-13 |20:08:52 |22:38:51 |2.499722000
|51 |2017-02-13 |2017-02-13 |22:59:18 |23:18:17 |0.316388000
|51 |2017-02-13 |2017-02-13 |23:33:44 |05:04:16 |5.508888000
|51 |2017-02-14 |2017-02-13 |02:52:02 |05:11:54 |2.331111000
|51 |2017-02-14 |2017-02-14 |20:09:23 |21:59:23 |1.833333000
|51 |2017-02-14 |2017-02-14 |22:30:28 |22:49:51 |0.323055000
|51 |2017-02-15 |2017-02-14 |05:05:17 |05:08:17 |0.050000000
每当我添加 TotalHours hours Group By WorkDate 时,它都会给出错误的答案.
Whenever I am adding TotalHours hours Group By WorkDate, it is giving incorrect answer.
推荐答案
也许你需要的是添加一个明确的总和:http://www.w3resource.com/sql/聚合函数/sum-with-group-by.php:
Perhaps what you need is to add an explicit sum: http://www.w3resource.com/sql/aggregate-functions/sum-with-group-by.php:
SELECT TOP 1 WorkDate, inOutMode, SUM (TotalHours) FROM tblAttendance t
WHERE t.EmpCode = I.EmpCode AND t.WorkDate > I.WorkDate
GROUP BY WorkDate, inOutMode
ORDER BY t.WorkDate
(未经测试,对不起,如果这没有帮助 - 我知道的 SQL 非常有限:P)
(untested, sorry if this doesn't help - I know very limited SQL :P)
这篇关于来自生物识别设备的考勤记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文