SQL在值更改时查询新列的枢轴值

Question

我正在寻找与员工打卡进出有关的查询的帮助。

我的代码是:

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

结果是:

UserIDtbody> <<tr>

日期	时间	UserName	EventSubTypeDescription
2022-05-09	07:53	393	詹妮弗	时钟在
2022-05-09	33	393	詹妮弗	打卡
2022-05-09	14:06	393	詹妮弗	时钟在
2022-05-09	16:57	393	詹妮弗	打卡
2022-05-09	07:59	401	机构2	时钟在
2022-05-09	12:58	401	机构2	打卡
2022-05-09	13:27	401	机构2	时钟在
2022-05-09	16:56	401	机构2	打卡
2022-05-09	07:57	422	小胡子	时钟在
2022-05-09	13:56	422	小胡子	打卡
2022-05-09	07:58	432	机构4	时钟在
2022-05-09	13:00	432	机构4	打卡
2022-05-09	13:30	432	机构4	时钟在
2022-05-09	16:56	432	机构4	打卡
2022-05-09	07:57	434	约旦	时钟在
2022-05-09	13:32	434	约旦	打卡
2022-05-09	14:03	434	约旦	时钟在
2022-05-09	16:59	434	约旦	打卡
2022-05-09	07:59	438	亚当	时钟在
2022-05-09	59	438	亚当	打卡
2022-05-09	13:29	438	亚当	时钟在
2022-05-09	16:56	438	亚当	打卡

从当前的输出表开始，您可以根据特定的日期和用户进行分区，为每一行分配列号。然后使用CASE构造上的MAX函数分别提取clock in,clock out,clock in 2,clock out 2的值:

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

在这里试试。

边注1:要将此查询与您的查询一起使用，只需替换output table

的代码即可。旁注2:为了更好的查询，避免您的中间结果(由output_table结果集给出)，如果您设法获得EventEx表，请在这里ping我。