sql - 如何使用 IF 条件与 SQL 比较日期
问题描述
我有一个来自考勤机的 MS ACCESS 表,它由 USERID、CHECKTIME 列组成。
使用 MS Access 中的以下 SQL 查询,我设法排序并找到 MIN(日期和时间)作为开始/打卡,MAX(日期和时间)作为每天打卡(日期/工作日期)
SELECT MIN(a.CHECKTIME) as work_start_time, MAX(a.CHECKTIME) as work_end_time, b.CheckDate as work_date, WEEKDAY(b.CheckDate,2) as work_day, a.USERID as user_id
FROM CHECKINOUT a RIGHT JOIN (
SELECT DISTINCT
DateValue(CHECKTIME) as CheckDate,
USERID FROM CHECKINOUT
WHERE ( MONTH(CHECKTIME) = 4 )
AND YEAR(CHECKTIME) = 2019 AND USERID =50
)
b on a.USERID = b.USERID
and DateValue(a.CHECKTIME) = b.CheckDate GROUP BY b.checkDate, a.USERID
查询结果:
Work_start_Time | work_end_time | Work_date | Work_day |User_ID
1/04/2019 8:30:19 AM | 1/04/2019 8:30:19 AM | 1/04/2019| 1 |50
2/04/2019 12:16:38 AM | 2/04/2019 9:40:52 AM | 2/04/2019| 2 |50
3/04/2019 1:25:29 AM | 3/04/2019 1:25:29 AM | 3/04/2019| 3 |50
4/04/2019 7:09:26 AM | 4/04/2019 9:10:37 PM | 4/04/2019| 4 |50
我想做的是,如果第二天(CHECKTIME +1)work_start_time 小于早上 5 点,那么第二天的 MIN(CHECKTIME)就变成了 Work_end_time。
SELECT MIN(a.CHECKTIME) as work_start_time,
IF (MIN(a.CHECKTIME+1) < 5:00:00 AM )
MIN(a.CHECKTIME+1) as work_end_time,
ELSE
MAX(a.CHECKTIME) as work_end_time,
b.CheckDate as work_date, WEEKDAY(b.CheckDate,2) as work_day, a.USERID as user_id
FROM CHECKINOUT a RIGHT JOIN (
SELECT DISTINCT
DateValue(CHECKTIME) as CheckDate,
USERID FROM CHECKINOUT
WHERE ( MONTH(CHECKTIME) = 4 )
AND YEAR(CHECKTIME) = 2019 AND USERID =50
)
b on a.USERID = b.USERID
and DateValue(a.CHECKTIME) = b.CheckDate GROUP BY b.checkDate, a.USERID
查询结果:
Work_start_Time | work_end_time | Work_date | Work_day |User_ID
1/04/2019 8:30:19 AM | 2/04/2019 12:16:38AM | 1/04/2019| 1 |50
2/04/2019 9:40:52 AM | 3/04/2019 1:25:29 AM | 2/04/2019| 2 |50
3/04/2019 1:25:29 AM | 3/04/2019 1:25:29 AM | 3/04/2019| 3 |50
4/04/2019 7:09:26 AM | 4/04/2019 9:10:37 PM | 4/04/2019| 4 |50
解决方案
尝试使用IIF:
IIF(TimeValue(MIN(a.CHECKTIME)) < #05:00:00#), MIN(a.CHECKTIME+1), MAX(a.CHECKTIME)) as work_end_time,
推荐阅读
- visual-studio-code - 无法在 VSC 中安装任何扩展,无法手动安装下载
- python - 对多个文档进行排序/比较的最佳方法?
- templates - 如何在 VueJS 中创建父布局
- sharepoint - 在没有隐藏列表的情况下获取站点中的所有列表
- jenkins - Jenkins 2.192 git hooks 请求中没有包含有效的面包屑
- javascript - 使用 JavaScript 动态显示数组数据
- python - 具有不同大小索引的 Pandas groupby(df.index)
- python - 如何解决此语法问题?我很困惑
- r - group_by 并创建一系列每月日期
- python - 在 Raspberry Pi (Buster)、内核 4.19、python 2.7 上使用 sudo 时找不到模块