Display all Employees In and Out Time of each Day in SQL Server

Last Reply one month ago By akhter

Posted one month ago

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              

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

I used this query but it return just on ID record 

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;  

 

You are viewing reply posted by: akhter one month ago.
Posted one month ago

I figure out this approach.

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