如何使用SQL查询从单行打孔机考勤日志中获取check - checkout时间差

Question

我们正在使用zkemkeeper.dll的考勤打卡机，并使用Vs.net 2010获得AttLog。如何从SQL查询中获取记录?我的表结构是这样的

LogID       int P.K.
DeviceIP    varchar
EnrollNo    int
AttDate     datetime
AttYear     int 
AttMonth    int
AttDay      int
AttTime     varchar

当前输出:

LogID   EnrollNo     AttDate    AttYear AttMonth  AttDay  AttTime
1   319 1/9/2011 9:55:00 PM 2011    8    31 9:55    
2   319 1/9/2011 18:30:00 PM    2011    8    31 18:30   
3   325 1/9/2011 10:00:00 PM    2011    8    31 10:00   
4   325 1/9/2011 18:35:00 PM    2011    8    31 18:35   

我想要这样的新输出，并计算输入输出时间差:

LogID  EnrollNo   AttDate          AttYear  AttMonth    AttDay  In    out    Diff
1   319 1/9/2011 9:55:00 PM 2011   8         31         9:55   18:30     8:35
2   325 1/9/2011 6:30:00 PM 2011   8         31         10:00   18:35    8:35

Answer 1

declare @t table (EnrollNo int, [Date] datetime, Time varchar(5))
insert @t select 1, '8-10-2011 12:00:32', '13:12' 
union all select 1, '8-10-2011 12:00:32', '23:14' 
union all select 2, '8-10-2011 12:00:32', '11:12' 
union all select 2, '8-10-2011 12:00:32', '20:14' 
union all select 3, '8-10-2011 12:00:35', '12:12' 
union all select 3, '8-10-2011 12:00:32', '23:14' 
union all select 4, '8-10-2011 12:00:32', '17:12' 
union all select 4, '8-10-2011 12:00:32', '23:14'
select 
  EnrollNo, 
  CAST(CONVERT(varchar, Date, 101) AS DateTime), 
  right('0' + cast(datediff(hour, cast(min(Time) as datetime), 
  cast(max(Time) as datetime)) as varchar(2)),2) + ':' + 
    right('0' + cast(datediff(minute, cast(min(Time) as datetime), 
    cast(max(Time) as datetime)) % 60 as varchar(2)),2),
  min(Time),
  max(Time)
from @t group by EnrollNo,CAST(CONVERT(varchar, Date, 101) AS DateTime)

Answer 2

试试这个…

SELECT timeff ("18:30"，"9:55") as Diff;

或

SELECT timeff (In,Out) as Diff;

输出:08:35:00

Answer 3

您是否希望在同一天获得相同的registrno的第一次和最后一次?尝试使用Group By来获得"In"one_answers"Out"字段，然后使用其他答案中的timemediff来计算差异:

SELECT EnrolNo, AttYear, AttMonth, AttDay, Min(AttDate) AS [In], Max(AttDate) AS [Out],
TIME_Format(TIMEDIFF([Out],[In]),'%H:%i') As [Diff] FROM Table GROUP BY EnrolNo, AttYear, AttMonth, AttDay;