Insert into SQL Server using Attendance Log file from fingerprint device?

Question

I get this file from fingerprint TA880 device :

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

I have tried:

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

This gives me:

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

How can I make a query in SQL Server 2008 to extract the data look like this:

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

My working time is: 08:00 to 17:00 (lunchtime : 12:00 to 13:00)

What have you tried so far? (btw, answer is `group by` + `min()` + `max()` + [extracting time from datetime](http://stackoverflow.com/a/7710495/628532) ) — Jan 'splite' K., Jul 23 '15 at 10:04

Steve Ford · Answer 1 · 2015-07-24T12:34:43.270

1

You are grouping by date/time not date. To correct group by the date:

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)

edited Jul 24 '15 at 12:34

answered Jul 23 '15 at 13:51

Steve Ford

7,433
19
40

Thank you! But how can I calculate the Hours? Pls! – Cát Tường Vy Jul 23 '15 at 14:02
@user3035133 although this is simpler! – Steve Ford Jul 24 '15 at 14:10

score 0 · Accepted Answer · answered Jul 23 '15 at 15:02

This is the answer for my question:

 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

Insert into SQL Server using Attendance Log file from fingerprint device?

2 Answers2