I have data in column now i want to separate it status wise one col Status(1)IN and Col(2)Out
hi
ok
i was able to figure it out
nothing to with left join
please let me know if ok .. thanks
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