分离列基值的数据 [英] Separat data on column base value

问题描述

我在这样的三栏中有记录

i have records in three column like this

EmpID	       DateTime	       StatusINOUT
1	    2018-05-26 08:44          1
1	    2018-05-26 08:44	      2
2	    2018-05-28 08:44	      1
2           2018-05-28 12:44	      2                 
1           2018-05-21 08:44	      1
1	    2018-05-21 10:44	      2
2	    2018-05-23 08:44	      1
2           2018-05-23 08:44	      2

< br $>




现在我想显示状态明智的INTIME（1）和OUTTIME（2）它如下

Now i want to display Status wise INTIME(1) and OUTTIME(2) it as below

Empid	INTIME(1)	           OUTIME(2)
1	 2018-05-26 08:44	 2018-05-26 08:44
2	 2018-05-28 08:44	 2018-05-28 12:44
1	 2018-05-21 08:44	 2018-05-21 10:44
2	 2018-05-23 08:44	 2018-05-23 08:44

我尝试过：



我试过下面的查询只是它返回一行对一个empID但我有一个针对一个员工ID的多个数据

What I have tried:

I have tried below query just it returning one row against one empID but i have multiple data against one ID of employee

select Empcode, s1 as 'INOUT (1)INTime', s2 as 'INOUT(2)OutTime' 
from 
( 
  select Empcode,Time,INOUT,Date
  from HR2 
) d 
pivot 
( 
  max(Date) 
  for INOUT in (s1,s2) 
) piv;

推荐答案

试试这个：

Try this:

Select EmpID, 
    MIN([DateTime]) as InTime, 
    MAX([DateTime]) as OutTime
FROM (SELECT EmpID, [DateTime], CAST([DateTime] AS DATE) AS DT FROM HR2) a
GROUP BY a.EmpID, a.DT

这篇关于分离列基值的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！