我正在寻求有关员工打卡/退房查询的帮助。
我的代码目前是：
SELECT CAST(EVENTTIME AS Date) AS Date, FORMAT(EVENTTIME, 'HH:mm') AS Time,SUM(UserID) AS UserID, FirstName + Space(1) + Surname, EventSubTypeDescription
FROM EventsEx
WHERE EventTime >= '2022-05-09' AND (PeripheralName ='clock (In)' OR PeripheralName ='clock (Out)')
GROUP BY userid, EventTime, FirstName, Surname, EventSubTypeDescription
ORDER BY Date,UserID, Time ASC结果是：
| Date | Time | UserID | UserName | EventSubTypeDescription |
|---|---|---|---|---|
| 2022-05-09 | 07:53 | 393 | Jennifer | Clock in |
| 2022-05-09 | 13:33 | 393 | Jennifer | Clock out |
| 2022-05-09 | 14:06 | 393 | Jennifer | Clock in |
| 2022-05-09 | 16:57 | 393 | Jennifer | Clock out |
| 2022-05-09 | 07:59 | 401 | agency 2 | Clock in |
| 2022-05-09 | 12:58 | 401 | agency 2 | Clock out |
| 2022-05-09 | 13:27 | 401 | agency 2 | Clock in |
| 2022-05-09 | 16:56 | 401 | agency 2 | Clock out |
| 2022-05-09 | 07:57 | 422 | Tash | Clock in |
| 2022-05-09 | 13:56 | 422 | Tash | Clock out |
| 2022-05-09 | 07:58 | 432 | agency 4 | Clock in |
| 2022-05-09 | 13:00 | 432 | agency 4 | Clock out |
| 2022-05-09 | 13:30 | 432 | agency 4 | Clock in |
| 2022-05-09 | 16:56 | 432 | agency 4 | Clock out |
| 2022-05-09 | 07:57 | 434 | Jordan | Clock in |
| 2022-05-09 | 13:32 | 434 | Jordan | Clock out |
| 2022-05-09 | 14:03 | 434 | Jordan | Clock in |
| 2022-05-09 | 16:59 | 434 | Jordan | Clock out |
| 2022-05-09 | 07:59 | 438 | Adam | Clock in |
| 2022-05-09 | 12:59 | 438 | Adam | Clock out |
| 2022-05-09 | 13:29 | 438 | Adam | Clock in |
| 2022-05-09 | 16:56 | 438 | Adam | Clock out |
每个用户在白天上班和下班。我需要将 Times 移动到单独的列，因此每个用户每天都有一行。
| Date | UserID | Username | EventSubTypeDescription | Clock in | Clock Out | Clock in 2 | Clock out 2 |
|---|---|---|---|---|---|---|---|
| 09/05/2022 | 393 | Jennifer | Clock in | 07:53 | 13:33 | 14:06 | 16:57 |
| 09/05/2022 | 401 | agency 2 | Clock in | 07:59 | 12:58 | 13:27 | 16:56 |
| 09/05/2022 | 422 | Tash | Clock in | 07:57 | 13:56 | ||
| 09/05/2022 | 432 | agency 4 | Clock in | 07:58 | 13:00 | 13:30 | 16:56 |
| 09/05/2022 | 434 | Jordan | Clock in | 07:57 | 13:32 | 14:03 | 16:59 |
| 09/05/2022 | 438 | Adam | Clock in | 07:59 | 12:59 | 13:29 | 16:56 |
回答1
从当前输出 table 开始，您可以通过对特定 date 和用户进行分区，为每一行分配一个列号。然后在 CASE 构造上使用 MAX 函数分别提取 clock in、clock out、clock in 2、clock out 2 的 values：
SELECT Date,
UserID,
Username,
MAX(CASE WHEN ColNum=1 THEN Time ELSE NULL END) AS Clock_in,
MAX(CASE WHEN ColNum=2 THEN Time ELSE NULL END) AS Clock_out,
MAX(CASE WHEN ColNum=3 THEN Time ELSE NULL END) AS Clock_in_2,
MAX(CASE WHEN ColNum=4 THEN Time ELSE NULL END) AS Clock_out_2
FROM (SELECT *,
ROW_NUMBER() OVER(PARTITION BY Date, UserID
ORDER BY Time ) ColNum
FROM output_table) ranked_clock_events
GROUP BY Date,
UserID,
Username
ORDER BY UserID试试https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=df4a03745201401b09a2ad840238d9e4。
旁注1：要将此查询与您的查询一起使用，只需将您的代码替换为 output table
旁注 2：为了更好地查询以避免中间结果（由 output_table 结果集给出），请在此处 ping 我以防您设法获得 EventEx table。