时间戳

首页 > 解决方案 > 查找同一列的 In 和 out

sql - 查找同一列的 In 和 out

问题描述

我在下面有一个表格，系统给出了我不想要的输出和输出，但下面给出了所需的输出。

EmployeeID  TrxType TrxDateTime 
1              IN   20/07/2020 7:00 
1             OU    20/07/2020 18:00    
2             IN    21/07/2020 6:00 
2             OU    21/07/2020 17:30

系统输出：-

employeeid  date    CheckIN            CheckOUT
1   20.07.2020        NULL           20/07/2020 18:00
1   20.07.2020     20/07/2020 7:00     NULL
2   21.07.2020        NULL           21/07/2020 17:30
2   21.07.2020     21/07/2020 6:00      NULL

这是我使用的查询：

select distinct(TimeBooking.employeeid),(convert(nvarchar(10), TimeBooking.PunchDateTime, 104)) as date,
    CheckIN = Max(case when TimeBooking.TrxType ='IN' then TimeBooking.trxDateTime END),
            CheckOUT = MAX(CASE WHEN TimeBooking.TrxType = 'OU' THEN TimeBooking.trxDateTime END)
            from TimeBooking where TimeBooking.TrxDateTime>='20-july-2020'  
    group by TimeBooking.TrxDateTime,employeeid

期望的输出：-

employeeid      date        CheckIN         CheckOUT
1           20.07.2020  20/07/2020 7:00   20/07/2020 18:00
2           21.07.2020  21/07/2020 6:00   21/07/2020 17:30

标签： sqlsql-server

解决方案

假设每个日期每个员工 ID 只有一行，只需使用聚合：

select employeeid, convert(date, TrxDateTime), min(TrxDateTime) as checkIN, max(TrxDateTime) as checkOUT
from t
group by employeeid, convert(date, TrxDateTime);

编辑：

如果每个员工每天有多行，您可以通过计数然后汇总来“对齐”它们：

select employeeid, dte,
       min(case when trxType = 'IN' then TrxDateTime end) as checkIN,
       max(case when trxType = 'OU' then TrxDateTime end) as checkOUT
from (select t.*, convert(date, t.TrxDateTime) as dte,
             row_number() over (partition by t.employeeid, convert(date, t.TrxDateTime), t.trxType order by t.TrxDateTime) as seqnum
      from t 
     ) t
group by employeeid, dte, seqnum;

这是一个 db<>fiddle。

推荐阅读