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

Question

我有一个问题 - 我有 2 个这样的表:

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 |
......

用户:

| 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 的初学者。所以，如果我错了，请指正我，请帮助我解决这个问题。

Answer 1

这里的技巧是建立一个包含所有日期的最终列表作为派生表或 cte。

然后您可以CROSS JOIN users 和 dates 表，然后 JOIN 回到 Attendance 数据，像以前一样分组和使用 MIN/MAX 聚合。

LEFT JOIN 可确保在至少一名员工上类的任何一天，始终有每位员工的记录。如果该员工根本没有签到/ checkout ，则出勤将为 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 Here

一个警告 - 这仅在员工在同一天办理入住和退房手续时才有效。如果员工通宵工作，事情就会变得更加复杂。