时间戳

首页 > 解决方案 > MS Access 查询考勤时间

database - MS Access 查询考勤时间

问题描述

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

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 是基于'time'）

这是图像在此处输入图像描述

请帮我做这个查询，

标签： databasevbams-access

解决方案

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

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

推荐阅读