时间戳

首页 > 解决方案 > 如何按日期分组并获得所有进出时间？

sql - 如何按日期分组并获得所有进出时间？

问题描述

我想在行中获取所有输入/输出事件。我的表看起来像这样；

事件：

 emp_id EVENTID     DT
 4176   3   2019-04-09 15:34:12.000
 4176   1   2019-04-09 08:55:13.000
 4176   3   2019-04-08 16:08:32.000
 4176   2   2019-04-08 15:08:18.000
 4176   1   2019-04-08 14:41:37.000
 4176   0   2019-04-08 08:45:13.000

我尝试了以下查询，但对该输出不满意

 select
t.emp_reader_id as empId,B.emp_name, cast(max(DT) as date) as Belongs_to,B.areaname as POINTID,
 max(iif(EVENTID = 0, DT, null)) as F1, max(iif(EVENTID = 1, DT, null)) as F2, max(iif(EVENTID = 2, DT, null)) as F3, max(iif(EVENTID = 3, DT, null)) as F4,
  format(dateadd(ss,Datediff(second,max(iif(EVENTID = 0, DT, null)),max(iif(EVENTID = 3, DT, null))),0),'HH:mm') as Worked from (   
select
    *, grp = sum(iif(EVENTID = 0, 3, 0) ) over (partition by emp_reader_id order by DT)
from        Trnevents ) t inner join employee B on t.emp_reader_id=B.emp_reader_id where b.emp_reader_id=4176
   group by t.emp_reader_id,t.grp,B.emp_name,t.Belongs_to,B.areaname
                  order by emp_reader_id,t.Belongs_to,F1,F2 asc

预期结果如下：

  emp_id       dt          f1        f2       f3      f4     hours
  4176     2019-04-08     08:45     14:41   15:08    16:08   06:41
  4176     2019-04-08     08:55       -        -       15:34   06:39

如上所述，我需要完全输出。在此先感谢

事件编号：

0 为 F1，1 为 F2，2 为 F3，3 为 F4

标签： sqlsql-server

解决方案

以下是在 oracle 数据库上。所以不要被“from dual”搞糊涂了，前导只是为了模拟输入数据。同样在 oracle trunc(date_time) 上返回仅日期部分。首先使用 which 创建 mydata 是模拟输入。然后接下来通过按日期获取绝对最小值/最大值来获取 f1f4。Next 通过说我们只想要日期不匹配 f1 和 f4 的那些行来计算 f2 和 f4。根据您的规则，emp+dat 有 2 行或 4 行。然后 final 将原始源与这 2 连接得到 f1,f2,f3,f4。不同是因为驱动是原装的

with mydata as (
    select '4176' emp_id, '3' eventid, to_date('2019-04-09 15:34:12', 'YYYY-MM-DD HH24:MI:SS') dt from dual union all
    select '4176' emp_id, '1' eventid, to_date('2019-04-09 08:55:13', 'YYYY-MM-DD HH24:MI:SS') dt from dual union all
    select '4176' emp_id, '3' eventid, to_date('2019-04-08 14:41:37', 'YYYY-MM-DD HH24:MI:SS') dt from dual union all
    select '4176' emp_id, '2' eventid, to_date('2019-04-08 15:08:18', 'YYYY-MM-DD HH24:MI:SS') dt from dual union all
    select '4176' emp_id, '1' eventid, to_date('2019-04-08 14:41:37', 'YYYY-MM-DD HH24:MI:SS') dt from dual union all
    select '4176' emp_id, '0' eventid, to_date('2019-04-08 08:45:13', 'YYYY-MM-DD HH24:MI:SS') dt from dual
),
getf1f4 as (
    select emp_id, trunc(dt) dt, count(*) num_rows, min(dt) f1, max(dt) f4
    from mydata
    group by emp_id, trunc(dt)
),
getf2f3 as (
    select ee.emp_id, getf1f4.dt,
           min(ee.dt) f2,
           max(ee.dt) f3
    from mydata ee join getf1f4 on ee.emp_id = getf1f4.emp_id and trunc(ee.dt) = getf1f4.dt
    where ee.dt not in ( getf1f4.f1, getf1f4.f4)
    group by ee.emp_id, getf1f4.dt
)
select distinct 
       ee.emp_id, getf1f4.dt, getf1f4.f1, getf2f3.f2, getf2f3.f3, getf1f4.f4
from mydata ee 
     join getf1f4 on ee.emp_id = getf1f4.emp_id and trunc(ee.dt) = getf1f4.dt
     left outer join getf2f3 on ee.emp_id = getf2f3.emp_id and trunc(ee.dt) = getf2f3.dt

推荐阅读