ms-access - 计算每月的及时出勤率
问题描述
我想计算每个特定日期的员工准时或早到的数量。但在示例中，我将特定的 USERID 作为附加条件。
这是我的 CHECKINOUT 表（共 39 行）：
USERID CHECKTIME CHECKTYPE VERIFYCODE SENSORID WorkCode sn UserExtFmt Update
1040 02/03/2020 6:54:50 I 1 3 0 0840060140610 0 02/03/2020 10:13:56
1040 02/03/2020 8:00:00 I 1 2 0 0840060140160 0 02/03/2020 10:50:20
1040 02/03/2020 16:34:37 I 1 5 0 2809731360643 0 26/03/2020 9:51:41
1040 03/03/2020 8:02:41 I 1 2 0 0840060140160 0 26/03/2020 9:50:49
1040 03/03/2020 16:45:00 I 1 5 0 2809731360643 0 26/03/2020 9:51:42
1040 03/03/2020 16:45:03 I 1 5 0 2809731360643 0 26/03/2020 9:51:42
1040 04/03/2020 7:57:46 I 1 2 0 0840060140160 0 26/03/2020 9:50:49
1040 04/03/2020 7:57:48 I 1 2 0 0840060140160 0 26/03/2020 9:50:49
1040 04/03/2020 17:01:53 I 1 2 0 0840060140160 0 26/03/2020 9:50:49
1040 04/03/2020 17:01:56 I 1 2 0 0840060140160 0 26/03/2020 9:50:49
1040 05/03/2020 8:03:45 I 1 2 0 0840060140160 0 26/03/2020 9:50:49
1040 05/03/2020 8:03:48 I 1 2 0 0840060140160 0 26/03/2020 9:50:49
1040 05/03/2020 16:41:02 I 1 5 0 2809731360643 0 26/03/2020 9:51:42
1040 05/03/2020 16:41:05 I 1 5 0 2809731360643 0 26/03/2020 9:51:42
1040 06/03/2020 8:27:13 I 1 2 0 0840060140160 0 26/03/2020 9:50:50
1040 06/03/2020 17:26:03 I 1 5 0 2809731360643 0 26/03/2020 9:51:42
1040 06/03/2020 17:26:06 I 1 5 0 2809731360643 0 26/03/2020 9:51:42
1040 07/03/2020 11:53:57 I 1 2 0 0840060140160 0 26/03/2020 9:50:50
1040 09/03/2020 8:01:51 I 1 2 0 0840060140160 0 27/03/2020 10:29:16
1040 16/03/2020 7:58:20 I 1 2 0 0840060140160 0 26/03/2020 9:50:52
1040 16/03/2020 7:58:22 I 1 2 0 0840060140160 0 26/03/2020 9:50:52
1040 16/03/2020 16:34:07 I 1 5 0 2809731360643 0 26/03/2020 9:51:43
1040 17/03/2020 7:59:05 I 1 2 0 0840060140160 0 26/03/2020 9:50:52
1040 17/03/2020 16:43:50 0 1 5 0 2809731360643 0 26/03/2020 9:51:44
1040 18/03/2020 8:00:43 I 1 5 0 2809731360643 0 26/03/2020 9:51:44
1040 18/03/2020 8:00:46 I 1 5 0 2809731360643 0 26/03/2020 9:51:44
1040 18/03/2020 16:30:23 I 1 2 0 0840060140160 0 26/03/2020 9:50:52
1040 19/03/2020 8:03:24 I 1 2 0 0840060140160 0 26/03/2020 9:50:53
1040 19/03/2020 17:13:44 I 1 2 0 0840060140160 0 26/03/2020 9:50:54
1040 20/03/2020 8:10:41 I 1 3 0 0840060140610 0 26/03/2020 9:51:10
1040 20/03/2020 8:10:44 I 1 3 0 0840060140610 0 26/03/2020 9:51:10
1040 20/03/2020 17:01:41 I 1 5 0 2809731360643 0 26/03/2020 9:51:44
1040 23/03/2020 8:00:07 I 1 2 0 0840060140160 0 26/03/2020 9:50:54
1040 23/03/2020 16:38:09 I 1 5 0 2809731360643 0 26/03/2020 9:51:45
1040 24/03/2020 7:59:08 I 1 5 0 2809731360643 0 26/03/2020 9:51:45
1040 24/03/2020 7:59:11 I 1 5 0 2809731360643 0 26/03/2020 9:51:45
1040 24/03/2020 16:39:30 I 1 2 0 0840060140160 0 26/03/2020 9:50:55
1040 24/03/2020 16:39:33 I 1 2 0 0840060140160 0 26/03/2020 9:50:55
1040 26/03/2020 8:10:31 I 1 3 0 0840060140610 0 26/03/2020 9:51:11
这是我的 CHECKEXACT 的样子：
EXACTID USERID CHECKTIME
404 1040 09/03/2020 8:01:51
我尝试使用SUM聚合函数和IIf条件来实现这一点，但不幸的是查询给了我错误的结果。
这是我的查询：
SELECT af.USERID, SUM(
IIf(af.CHECKTIME Is Not Null,
IIf(WeekDay(DateValue(af.CHECKTIME)) <> 6 And Format(af.CHECKTIME, 'hh:nn:ss') <= '08:15:00',
1, IIf(Format(af.CHECKTIME, 'hh:nn:ss') <= '08:30:00', 1, 0)
),
IIf(bf.CHECKTIME Is Not Null,
IIf(WeekDay(DateValue(bf.CHECKTIME)) <> 6 And Format(bf.CHECKTIME, 'hh:nn:ss') <= '08:15:00',
1, IIf(Format(bf.CHECKTIME, 'hh:nn:ss') <= '08:30:00', 1, 0)
), 0
)
)
) AS [Came On Time or Early]
FROM (CHECKINOUT AS af
LEFT JOIN CHECKEXACT bf ON af.USERID = bf.USERID)
WHERE af.USERID = 1040 And af.CHECKTIME Between #3/1/2020# And #3/31/2020# GROUP BY af.USERID
上面的查询返回这个结果：
USERID Came On Time or Early
1040 441
正如我们所知，如果我们COUNTCHECKINOUT 表将返回 39 行，而 CHECKEXACT 仅返回 1 行。但是查询返回 441 作为 [Come On Time or Early]。
我不知道我的查询有什么问题，我想我提出了正确的查询，以获得 2020 年 3 月 USERID = 1040 的员工的准时或提前到达总数。
你能告诉我我的查询有什么问题吗？
解决方案
感谢@June7 通过评论回答这个问题。
多次检查后，我意识到我写了错误的查询，尤其是在这一行：
IIf(WeekDay(DateValue(af.CHECKTIME)) <> 6 And Format(af.CHECKTIME, 'hh:nn:ss') <= '08:30:00',
1, IIf(Format(af.CHECKTIME, 'hh:nn:ss') <= '08:30:00', 1, 0)
)
我应该分开...WeekDay(DateValue(af.CHECKTIME)) <> 6...到另一部分。如果 af.CHECKTIME 时间大于 '08:30:00' 而当天是星期五，我之前的查询将需要星期五不是星期五。然后会跳到IIf我想在另一个工作日进行操作的错误部分。
我也应该改变这一行：
...
FROM (CHECKINOUT AS af
LEFT JOIN CHECKEXACT bf ON af.USERID = bf.USERID)
...
对此：
...
FROM(
SELECT af.USERID, MIN(af.CHECKTIME) AS [Tanggal dan Waktu]
FROM CHECKINOUT AS af
WHERE af.USERID = 1040 And af.CHECKTIME Between #3/1/2020# And #3/31/2020#
GROUP BY af.USERID, DateValue(af.CHECKTIME)
UNION
SELECT bf.USERID, MIN(bf.CHECKTIME) AS [Tanggal dan Waktu]
FROM CHECKEXACT AS bf
WHERE bf.USERID = 1040 And bf.CHECKTIME Between #3/1/2020# And #3/31/2020#
GROUP BY bf.USERID, DateValue(bf.CHECKTIME)
)
...
因为在前面的查询中，From将打印给定范围之间的所有日期时间，并LEFT JOIN CHECKEXACT打印 CHECKEXACT 的数据，即只有一行（09/03/2020 8:01:51每次检查都是正确的，因为小于'08:30:00'和偏离当然会打印1）重复与 CHECKINOUT 的行一样多，而我只需要检查是否存在，这只是 CHECKINOUT 和 CHECKEXACT 每天的最小日期时间。
所以正确的完整查询应该是这样的：
SELECT USERID,
SUM(IIf(WeekDay(DateValue([Tanggal dan Waktu])) <> 6,
IIf(TimeValue([Tanggal dan Waktu]) <= TimeValue('08:30:00'),
1, 0
),
IIf(TimeValue([Tanggal dan Waktu]) <= TimeValue('08:30:00'), 1, 0)
)
)
AS [Came On Time or Early]
FROM(
SELECT af.USERID, MIN(af.CHECKTIME) AS [Tanggal dan Waktu]
FROM CHECKINOUT AS af
WHERE af.USERID = 1040 And af.CHECKTIME Between #3/1/2020# And #3/31/2020#
GROUP BY af.USERID, DateValue(af.CHECKTIME)
UNION
SELECT bf.USERID, MIN(bf.CHECKTIME) AS [Tanggal dan Waktu]
FROM CHECKEXACT AS bf
WHERE bf.USERID = 1040 And bf.CHECKTIME Between #3/1/2020# And #3/31/2020#
GROUP BY bf.USERID, DateValue(bf.CHECKTIME)
)
GROUP BY USERID
上述查询将打印6为正确/所需的[Came On Time or Early]记录。
推荐阅读
- ios - iOS Core Data - Double (NSNumber) 值在我第二次访问时更改
- python - 从 Mask_RCNN 张量中检索信息
- apache-spark - ES：索引模板不会从 UNIX 时间戳转换为日期
- r - 有没有办法产生类似于 mutate_at 或 mutate_if 的gather_at 或gather_if 函数
- mysql - Mysql 如果不存在会在存储过程中给我一个错误
- java - 从 servlet 向 java 类发送参数
- vue.js - 尝试加载静态 CSS 文件在 Vue JS 应用程序中给出 404
- c++ - CLion 是否不支持 .lib 文件（由 Visual Studio 生成）
- c# - 在我的 C# Windows 窗体中从 Linq 查询创建数据表
- python - python依赖项仅在CI机器上失败