sql - 按日期透视 DateTime 字段

Question

我有一个包含员工“打卡”（打卡/打卡）的表格，每个打卡可以是“打卡”（打卡类型 = 1）或“打卡”（打卡类型 = 2）。

该表的格式如下：

    emp_num | report_date | punch_time             | punch_type
    -----------------------------------------------------------
    1       | 2018-04-20  |2018-04-20 04:46:00.000 | 1
    1       | 2018-04-20  |2018-04-20 06:58:00.000 | 2
    1       | 2018-04-20  |2018-04-20 08:10:00.000 | 1
    1       | 2018-04-20  |2018-04-20 12:00:00.000 | 2 

我试图在同一行中获得第一个“打卡”（打卡）和下一个“打卡”（打卡）。那么，当然，任何后续都是一样的。

期望的输出：

    emp_num | report_date | punch_in               | punch_out
    -----------------------------------------------------------
    1       | 2018-04-20  |2018-04-20 04:46:00.000 | 2018-04-20 06:58:00.000
    1       | 2018-04-20  |2018-04-20 08:10:00.000 | 2018-04-20 12:00:00.000

请记住，如示例所示，一天内可能会有多个打入/打出组合。

任何帮助将不胜感激！

标签： sqlsql-serverpivotcase

Answer 1

首先你要知道哪个打卡时间属于哪个打卡时间。答：第n次打卡时间属于第n次打卡时间。所以给你的记录编号：

select
  p_in.emp_num,
  p_in.report_date,
  p_in.punch_time as punch_in,
  p_out.punch_time as punch_out
from
(
  select
    emp_num,
    report_date,
    punch_time,
    row_number() over (partition by emp_num, report_date order by punch_time) as rn
  from mytable
  where punch_type = 1
) p_in
left join
(
  select
    emp_num,
    report_date,
    punch_time,
    row_number() over (partition by emp_num, report_date order by punch_time) as rn
  from mytable
  where punch_type = 2
) p_out on p_out.emp_num = p_in.emp_num
        and p_out.report_date = p_in.report_date
        and p_out.rn = p_in.rn
order by p_in.emp_num, p_in.report_date, punch_in;