SQLTeam.com | Weblogs | Forums

Separate data in column as per status INTIME and OUTIME on the basis of Status 1 and 2


#1

I have data in column now i want to separate it status wise one col Status(1)IN and Col(2)Out


#4

hi

ok
i was able to figure it out
nothing to with left join

please let me know if ok .. thanks
:slight_smile:
:slight_smile:

drop create data ..
use tempdb 

go 


drop table data
go 


create table data
(
empid int null, 
datetime datetime null,
StatusINOUT int null ,
rn int identity(1,1) 
)
go 

insert into data select 1,'2018-05-26 08:44',1
insert into data select 1,'2018-05-26 08:44',2

insert into data select 2,'2018-05-28 08:44',1
insert into data select 2,'2018-05-28 12:44',2

insert into data select 1,'2018-05-21 08:44',1
insert into data select 1, '2018-05-21 10:44',2

insert into data select 2,'2018-05-23 08:44',1
insert into data select 2,'2018-05-23 08:44',2

insert into data select 3,'2018-05-11 08:44',2
go 

select * from data 
go
SQL
SELECT empid, 
           Min(CASE 
                 WHEN statusinout = 1 THEN datetime 
               END), 
           Max(CASE 
                 WHEN statusinout = 2 THEN datetime 
               END) 
    FROM   data 
    GROUP  BY ( rn + 1 ) / 2, 
              empid
Results