使用 MS Access 或 SQL Server 的出勤日志

Question

我工作了将近一个月，但我想我现在需要一些帮助。我在下面有一个时间日志。我正在使用MS Access和C#。请帮助什么选择查询

ID  BADGE   CHECKTIME
-----------------------------
1   1507010 5/31/2018 8:51
1   1507010 5/31/2018 19:52
2   1708004 5/31/2018 6:35
2   1708004 5/31/2018 13:43
3   1708005 5/31/2018 19:23
3   1708005 6/1/2018 8:34
4   1708006 5/31/2018 7:51
4   1708006 6/1/2018 18:34
5   1708007 5/31/2018 19:23
5   1708007 6/1/2018 6:36
6   1708009 5/31/2018 7:11
6   1708009 5/31/2018 7:12
6   1708009 5/31/2018 22:02
6   1708009 5/31/2018 22:03

我想成为这个。请帮忙。 获取此数据的最佳查询。

ID  Badge   IN              OUT
--------------------------------------------
1   1507010 5/31/2018 8:51  5/31/2018 13:43
2   1708004 5/31/2018 6:35  5/31/2018 13:43
3   1708005 5/31/2018 19:23 6/1/2018 8:34
4   1708006 5/31/2018 7:51  6/1/2018 18:34
5   1708007 5/31/2018 19:23 6/1/2018 6:36
6   1708009 5/31/2018 7:12  5/31/2018 22:03

Answer 1

以下查询应接近所需内容：

SELECT
ID,
Badge,
MIN(CHECKTIME) AS [IN],
MAX(CHECKTIME) AS [OUT]
FROM yourTable
GROUP BY
ID,
Badge;

我对徽章1708009的预期输出有疑问，因为该徽章的最早检查时间是7:11，而不是7:12。

Answer 2

我会在subquery中使用row_number()：

select id, badge, min(checktime) as in, max(checktime) as out
from (select *,  row_number() over (partition by id, badge, cast(checktime as date), datepart(hh,checktime) 
order by datepart(mm,checktime) desc) seq
from table 
) t
where seq = 1
group by id, badge;