MS Access考勤考勤查询
MS Access考勤考勤查询
提问于 2020-02-15 16:23:05
我正在做一个涉及时间和考勤管理的项目。当我从生物识别读取器下载数据时，我得到了以下格式的记录，
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是基于‘时间’的)
这是一张图片
请帮我做这个查询，
回答 1
Stack Overflow用户
回答已采纳
发布于 2020-02-15 18:21:04
您可以按用户by和日期分组，然后使用条件聚合：
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页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/60237040
复制相关文章
相似问题
腾讯云开发者
