MS Access查询出入勤时间 [英] MS Access Query for time In/Out attendance

问题描述

我正在做一个涉及时间和考勤管理的项目.从生物识别读取器下载数据时，我得到了以下格式的记录，

I'm doing a project which involves time and attendance management. When I download data from the biometric reader, I got the records in the following format,

USERID  CHECKTIME
5001    12/09/2011  09:05:34
5002    12/09/2011  09:33:13
5001    12/09/2011  13:05:53
5002    12/09/2011  13:22:24
5001    12/09/2011  14:05:22
5002    12/09/2011  14:33:53
5001    12/09/2011  18:05:09
5002    12/09/2011  17:44:34

这是图像

我想显示以下记录， (Log_In，LB_Out，LB_In，Log_Out，WorkTime和LunchBreak基于时间")

I want to show the above records as follows, (the Log_In, LB_Out, LB_In, Log_Out, WorkTime and LunchBreak are based on 'time')

这是图像

请帮助我进行此查询，

推荐答案

您可以按用户ID和日期进行分组，然后使用条件聚合:

You can group by userid and date and then use conditional aggregation:

select t.userid, datevalue(t.checktime) as [date],
  max(iif(t.counter = 0, t.checktime, null)) as Log_In,
  max(iif(t.counter = 1, t.checktime, null)) as LB_Out,
  max(iif(t.counter = 2, t.checktime, null)) as LB_In,
  max(iif(t.counter = 3, t.checktime, null)) as Log_Out,
  Format((Log_In - LB_Out) + (LB_In - Log_Out), "HH:mm:ss") as WorkTime,
  Format(LB_In - LB_Out, "HH:mm:ss") as LunchBreak
from (
  select t.*,
    (select count(*) from tablename where userid = t.userid and datevalue(checktime) = datevalue(t.checktime) and checktime < t.checktime) as counter
  from tablename as t
) as t
group by t.userid, datevalue(t.checktime)

结果:

userid  date        Log_In                  LB_Out                  LB_In                   Log_Out                 WorkTime  LunchBreak
5001    12/9/2011   12/9/2011 9:05:34 am    12/9/2011 1:05:53 pm    12/9/2011 2:05:22 pm    12/9/2011 6:05:09 pm    08:00:06  00:59:29
5002    12/9/2011   12/9/2011 9:33:13 am    12/9/2011 1:22:24 pm    12/9/2011 2:33:53 pm    12/9/2011 5:44:34 pm    06:59:52  01:11:29

这篇关于MS Access查询出入勤时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！