Attendance IN and OUT

Question

Consider the following table structure and sample data -

EmpID InputDateTime      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 

Now I want to separate column InputDateTime into two columns i.e., INTIME(1) and OUTTIME(2). The logic behind this is the date for which StatusInOut is 1 will be InTime and for StatusInOut is 2 that date value will be OUTTIME(2).

The expected output format is as shown 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

Answer 1

use case when

select empid,max(case when statusINOut=1 then Datetime end)  as INtime,
max(case when statusINOut=2 then Datetime end)  as Outtime
from table_name t
group by empid,convert(date,Datetime)

Answer 2

try:

select EmpID
  , min(DateTime) INTIME(1)
  , max(DateTime) OUTIME(2)
from TABLE
group by EmpID;

Answer 3

Try the following query using join and update.

create table #tempStatus (EmpId int, intTime datetime, sStatus int)        
insert into #tempStatus        
values(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)  
    ,(3, '2018-05-23 08:44', 1)  

select EmpId, MIN(intTime) as intTime, MAX(intTime) as OutTime into #tempA from (      
select EmpId, intTime, intTime as OutTime      
from #tempStatus where sStatus = 1      
)a       
group by EmpId, intTime      

update s      
set s.OutTime = t.outTime      
from #tempA s      
left join     
(    
select EmpId, MAX(outTime) as outTime from(       
select EmpId, intTime as outTime      
from #tempStatus where sStatus = 2      
)b       
group by empId,outTime) t     
on s.EmpId = t.EmpId and Convert(Varchar,s.OutTime,112) =  Convert(Varchar,t.outTime,112)      

select * from #tempA order by EmpId      

drop table #tempA      
DROP TABLE #tempStatus

OR you can also try the below one query

select empid, 
       max(case when sStatus = 1 then intTime end) as INTIME,
       max(case when sStatus = 2 then intTime end) as OUTIME
from (select t.*, 
             row_number () over ( order by inttime) as seq1,
             row_number () over (partition by empid order by inttime) as seq2
      from #tempStatus t
     ) t
group by empid, (seq1-seq2);

Check for the similar answers - here