为每个日期单独打卡和打卡

Question

我有一个简单的表格，记录人们的打卡和打卡，就像这样。

Id     | EmployeeNumber | InOutDateTime
-----------------------------------------------------
1      | 505            | 2020-03-24 08:32:42:000
2      | 506            | 2020-03-24 08:35:47:000
3      | 507            | 2020-03-24 08:46:12:000
4      | 505            | 2020-03-24 16:59:00:000
5      | 506            | 2020-03-24 17:05:00:000
6      | 508            | 2020-03-24 17:46:12:000

我想把每个日期的签到和签退分开。

因此，基于上面的表，我希望获得以下结果：

EmployeeNumber | InDateTime               | OutDateTime
----------------------------------------------------------------------
505            | 2020-03-24 08:32:42:000  | 2020-03-24 16:59:00:000
506            | 2020-03-24 08:35:47:000  | 2020-03-24 17:05:00:000
507            | 2020-03-24 08:46:12:000  | null
508            | null                     | 2020-03-24 17:46:12:000

我一直在玩子查询和分组，但没有得到它。有人能帮上忙吗？

Answer 1

一种可能的解决方案是以下语句，但重要的问题是，对于单个EmployeeNumber输入，如何确定InOutDateTime是In还是Out：

表：

CREATE TABLE Data (
   Id int,
   EmployeeNumber int,
   InOutDateTime datetime
)
INSERT INTO Data (Id, EmployeeNumber, InOutDateTime)
VALUES
   (1, 505, '2020-03-24T08:32:42'),
   (2, 506, '2020-03-24T08:35:47'),
   (3, 507, '2020-03-24T08:46:12'),
   (4, 505, '2020-03-24T16:59:00'),
   (5, 506, '2020-03-24T17:05:00'),
   (6, 508, '2020-03-24T17:46:12')

声明：

SELECT 
   EmployeeNumber, 
   MIN(CASE WHEN DATEPART(hour, InOutDateTime) <= 12 THEN InOutDateTime END) AS InDateTime,
   MAX(CASE WHEN DATEPART(hour, InOutDateTime) > 12 THEN InOutDateTime END) AS OutDateTime
FROM Data
GROUP BY EmployeeNumber, CONVERT(date, InOutDateTime)
ORDER BY EmployeeNumber, CONVERT(date, InOutDateTime)

结果：

EmployeeNumber  InDateTime              OutDateTime
505             2020-03-24 08:32:42.000 2020-03-24 16:59:00.000
506             2020-03-24 08:35:47.000 2020-03-24 17:05:00.000
507             2020-03-24 08:46:12.000 
508                                     2020-03-24 17:46:12.000