如何在SQL Server中为每个用户创建所有的考勤记录

Question

我是一位有用的助手，可以为您翻译文本。

我有一个问题 - 我有两个这样的表：

tblattLogs：

| user_id | attendantLogs    |
+---------+------------------+
|   01    | 2017-10-31 08:00 |
|   01    | 2017-10-31 12:00 |
|   01    | 2017-10-31 17:05 |
|   01    | 2017-10-31 17:10 |
|   02    | 2017-10-31 08:10 |
|   02    | 2017-10-31 11:00 |
|   02    | 2017-10-31 17:01 |
|   02    | 2017-10-31 17:05 |
......

tblusers:

| id | name | otherstuff |..
+----+------+------------+
| 01 | Joe  | otherstuff |
| 02 | Jean | otherstuff |
...

我想要以下结果：

| id | name | date       | CheckIn | CheckOut |
+----+------+------------+---------+----------+
| 01 | Joe  | 2017-10-31 | 08:00   | 17:10    |
| 02 | Jean | 2017-10-31 | 08:10   | 17:05    |
...

我的查询看起来像这样：

SELECT DISTINCT
    t.[user_id],
    MIN(t.attendantLogs) OVER (PARTITION BY [user_id]) AS CheckIn,
    MAX(t.attendantLogs) OVER (PARTITION BY [user_id]) AS CheckOut,
    n.name AS NAME 
FROM
    tblattLogs t, tblusers as n 
WHERE
    t.user_id = n.id

我的问题是：我该如何创建一个查询，以展示我想要的正确结果？而且我知道我的查询是错误的。我在SQL方面完全是个初学者。因此，如果我错了，请纠正我，并请帮我解决这个问题。

- Run

---

我猜你想要每个员工每天一条记录，并且你还需要指示哪些员工那天根本没有上班。此外，你的员工是否会进行夜班工作，例如晚上10点打卡，第二天早上6点打卡下班？ - StuartLC

@StuartLC 是的，那正是我想要的。不，我不需要移动记录，只需要每天的记录。 - Run

不良习惯：使用旧式JOIN语句。旧式的“逗号分隔表列表”已经在25年前被ANSI-92 SQL标准中更为合适的ANSI JOIN语法所取代，因此不建议使用。 - marc_s

Answer 1

一种方法是使用group by。演示，基于Stuart的数据。

;with cte
as
(
select user_id,
cast(attendant_logs as date) as dt,
min(cast(attendant_logs as time)) as checkin,
max(cast(attendant_logs as time)) as checkout
from
#tblattLogs
group by user_id,
cast(attendant_logs as date) 

)
select u.id,u.name,
c.*
from
#tblusers u
join
cte c
on c.user_id=u.id

- TheGameiswar

---

很高兴你觉得它有帮助。今后请像Stuart发布的那样发布SQLFIddle，这将帮助你更快地获得答案并获得赞同。 - TheGameiswar

@TheGameiswar 出于兴趣，SqlFiddle有一个很棒的功能叫做“TextToDDL” - 基本上，你可以以几乎任何格式粘贴OP的表结构，为表提供一个名称，然后它会为你创建DDL和INSERT脚本。节省了大量时间 :) - StuartLC

Answer 2

这里的技巧是建立一个定义所有日期的派生表或CTE列表。

然后，您可以将用户和日期表进行CROSS JOIN，然后再加入出勤数据，像之前一样分组并使用MIN / MAX聚合函数。

LEFT JOIN确保每个员工在至少有一名员工上班的任何一天都会有一条记录。如果该员工没有签到/签退，则出勤将为NULL。

WITH cteUniqueDates AS
(
  SELECT DISTINCT CAST(attendantLogs AS DATE) as AttendanceDate
  FROM tblattLogs
)
SELECT u.id, u.name, d.AttendanceDate, min(attendantLogs) AS CheckIn, 
       max(attendantLogs) AS CheckOut
FROM tblusers u
  CROSS JOIN cteUniqueDates d
  LEFT OUTER JOIN tblattLogs t
    ON u.Id = t.user_id AND CAST(attendantLogs AS DATE) = d.AttendanceDate
GROUP BY u.id, u.name, d.AttendanceDate
ORDER BY d.AttendenceDate, u.id;

在这里使用SqlFiddle

一个注意事项 - 这只适用于员工在同一天签到和签退的情况。如果员工通宵工作，则情况会变得更加复杂。

- StuartLC

---

这个很好。谢谢。但是，如何在一个查询中完成呢？抱歉我真的很新于 SQL。谢谢。 - Run

这是一个查询。另一种选择是将cteUniqueDates移动到派生表中，但这只是样式偏好。有一件事 - 我错过了您只想在签到时使用时间 - 按照GameIsWar的建议使用CAST(.. as TIME)。 - StuartLC

Answer 3

这里有一个单一的SQL查询，可以帮助您实现上述结果：

SELECT U.ID,
       U.name,
       CONVERT(DATE, MIN(L.attendantLogs)) [Date],
       CONVERT(VARCHAR(8), MIN(L.attendantLogs), 108) [CheckIn],
       CONVERT(VARCHAR(8), MAX(L.attendantLogs), 108) [CheckOut]
FROM tblattLogs L
     INNER JOIN tblusers U ON U.id = L.USER_ID
GROUP BY U.id,
         U.name;

结果 :

ID  name    Date        CheckIn     CheckOut
01  Joe     2017-10-31  08:00:00    17:10:00
02  Jean    2017-10-31  08:10:00    17:05:00

- Yogesh Sharma

---

我猜 OP 想要每个员工每天工作的分别签到/签退行。 - StuartLC

@YogeshSharma 是的，抱歉，如果我想在一个月内显示所有日志怎么办？谢谢。 - Run

@Run 无法理解您的意思，请编辑问题并在那里添加期望的输出。 - Yogesh Sharma