代码之家 › 专栏 › 技术社区 › Cát TÆ°á»ng Vy

是否使用指纹设备中的考勤日志文件插入SQL Server?

datetime date sql-server-2008 sql-server

0

Cát TÆ°á»ng Vy · 技术社区 · 11 年前

我从指纹TA880设备获取此文件:

Id User_id VerifyMode InOutMode PuchTime
--------------------------------------------
1   152        1          0 2015-07-17 09:38:03
2   184        1          0 2015-07-17 16:56:43
3   152        1          0 2015-07-17 16:57:18
4   165        1          0 2015-07-17 16:57:43
5   70         1          0 2015-07-17 16:57:59
6   134        1          0 2015-07-17 16:58:28
7   276        1          0 2015-07-17 16:59:04
8   278        2          0 2015-07-17 16:59:05
9   271        1          0 2015-07-17 16:59:10
10  268        1          0 2015-07-17 16:59:13
11  284        1          0 2015-07-17 16:59:16
12  364        1          0 2015-07-17 16:59:35
13  19         1          0 2015-07-17 16:59:38
14  381        1          0 2015-07-17 17:01:12
15  73         1          0 2015-07-17 17:12:31
16  126        1          0 2015-07-17 17:12:36
17  382        1          0 2015-07-17 17:13:50
18  53         1          0 2015-07-18 06:34:13
19  284        1          0 2015-07-18 08:05:17

我尝试过:

SELECT User_id,VerifyMode, CAST(PunchTime as DATE) Check_Date,
       MIN(CAST(PunchTime as Time))[TimeIN], 
       MAX(Cast(PunchTime as Time))[TimeOUT]
FROM dbo.AttLog
GROUP BY User_id,VerifyMode, PunchTime

这给了我:

User_id  Check_Date   TimeIN    TimeOUT   
-----------------------------------------
152      2015-07-17    09:38:03 09:38:03 
152      2015-07-17    16:56:43 16:56:43

如何在SQL Server 2008中进行查询以提取数据,如下所示:

User_id  Check_Date   TimeIN    TimeOUT   Hours
------------------------------------------------
152      2015-07-17    09:38:03 16:56:43  7:18:40

我的工作时间是:08:00至17:00(午餐时间:12:00至13:00)

2 回复 | 直到 11 年前

1

Steve Ford 11 年前

您是按日期/时间而不是日期分组的。要按日期更正组:

SELECT User_id,VerifyMode, CAST(PunchTime as DATE) Check_Date,
       MIN(CAST(PunchTime as Time))[TimeIN], 
       MAX(Cast(PunchTime as Time))[TimeOUT],
       CAST(MAX(PunchTime) - MIN(PunchTime) AS Time) As [Hours]
FROM dbo.AttLog
GROUP BY User_id,VerifyMode, CAST(PunchTime as DATE)

2

0

Cát TÆ°á»ng Vy 11 年前

这是我问题的答案:

 SELECT  User_id,VerifyMode, CAST(PunchTime as DATE)Check_Date,MIN(CAST(PunchTime as Time))[TimeIN], MAX(Cast(PunchTime as Time))[TimeOUT],
RIGHT('0' + CAST(DATEDIFF(SECOND, MIN(CAST(PunchTime as Time)), MAX(Cast(PunchTime as Time))) / 3600 AS VARCHAR),2) + ':' +
RIGHT('0' + CAST((DATEDIFF(SECOND, MIN(CAST(PunchTime as Time)), MAX(Cast(PunchTime as Time))) / 60) % 60 AS VARCHAR),2)  + ':' +
RIGHT('0' + CAST(DATEDIFF(SECOND, MIN(CAST(PunchTime as Time)), MAX(Cast(PunchTime as Time))) % 60 AS VARCHAR),2)as Hours
FROM dbo.AttLog 
GROUP BY User_id,VerifyMode, CAST(PunchTime as DATE)
Order by User_id