Separat data in column SttusINOUT wise into two columns

Akhter HUssain

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

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

14 Replies

Know the answer? Post it — somebody with the same question will find it here.

Suraj Kumar7y ago
Accepted answer
The column of #tempA is what you are getting in output - EmpId , intTime and outTime.

You can handle null date which is not available in outtime using left join as shown below.
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
You can find the live demo here- https://rextester.com/FJFR45676
+1
Akhter HUssain7y ago
Dear Suraj Kumar

here is my soultion,thanks for your guidance..

SELECT empcode, MAX(CASE WHEN INOUT = 1 THEN Date END) AS INtime, MAX(CASE WHEN INOUT = 2 THEN Date END) AS Outtime
FROM HR AS t
GROUP BY empcode, CONVERT(date, Date)

0
Suraj Kumar7y ago
Try the following query also
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);
0
Suraj Kumar7y ago
In real life scenario this doesn't happen but I am giving you hint that you can do it using union. I request you to accept the answer and I will try to help again.
0
Akhter HUssain7y ago
Your this query is working fine if OUTTIME(2) does not exit but if INTIME(1) does not exit then it does not show row .

check ID 3 Outime exit last row

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

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

0
Akhter HUssain7y ago
Thanks little bit more your support require that if INTIME does not exit and OUTTime exit i mean both condition applicable of INTime and OUTIME exitance like this ...thanks again ..

please guide ...

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

0
Akhter HUssain7y ago
what is the columns of #tempA s table ,and other query is that if INTIME(1) exit and OUTTIME(2) does not exit then how it will handle ,like thise

EmpId INTIME OUTTIME

2000091 26-May-2018 09:44:00 AM 26-05-2018 08:35 PM

2000091 27-May-2018 11:44:00 AM

2000092 27-05-2018 08:35 PM

+1
Suraj Kumar7y ago
Hello Akhter, you can get the output with the help of two tables as shown below
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)

select EmpId, MIN(intTime) as intTime, MIN(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
inner 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
Hope this will accept you and you might be known that writting code may slightly differ from developer to developer.
I can understand your query and it is good to query & find out new way to solve queries.

Here we have to only get the data of minimum date values of status is 1 for InTime and update it with OutTime value using inner join which is in status 2.

I have already deleted unuseful query written due to misunderstanding.
+1
Akhter HUssain7y ago
i have just one table here you are using three table
+1
Suraj Kumar7y ago
Please find the modified query as shown below
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)

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

select EmpId, MAX(outTime) as outTime into #tempB from(
select EmpId, intTime as outTime
from #tempStatus where sStatus = 2
)b
group by empId,outTime

update s
set s.OutTime = t.outTime
from #tempA s
inner join #tempB 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 #tempB
DROP TABLE #tempStatus
Now the output is as shown below
EmpId   intTime OutTime
-------------------------------------------------------------------------
1   2018-05-21 08:44:00.000 2018-05-21 10:44:00.000
1   2018-05-26 08:44:00.000 2018-05-26 08:44:00.000
2   2018-05-28 08:44:00.000 2018-05-28 12:44:00.000
2   2018-05-23 08:44:00.000 2018-05-23 08:44:00.000
Hope this is the correct and final output as expected.
+1
Akhter HUssain7y ago
Please Suraj Kumar try to understand this

i want this result

EmpId INTIME OUTTIME

2000091 26-May-2018 09:44:00 AM 26-05-2018 08:35 PM

2000091 27-May-2018 11:44:00 AM 27-05-2018 08:35 PM

2000092 27-May-2018 08:44:00 AM 27-05-2018 08:35 PM

+1
Akhter HUssain7y ago
output coming like this

it getting repeat in row but i want out time like this

EmpId IN OUT

2000091 26-May-2018 4:44:00 AM 26-05-2018 08:35 PM
+1

Akhter HUssain7y ago

Dear Suraj Kumar

I want pick same date 1 and 2 status against one empID

like this below

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

Sreekanth Reddy7y ago
Hi Akhter,

-- Getting all IN'S and OUT'S into separate temp table
select * into #tempIn where StatusInOUT = 1;
select * into #tempOut where StatusInOUT = 2;

--Getting all disting empid values into another temp table
select distinct EmpID into #EmpIdTbl;

-- Creating another temp for storing our final result.
create table #AttendanceTbl(int empid, varcahr INTIME, varcahr OUTIME);

--Looping all out employees
While (Select Count(*) From #EmpIdTbl > 0)
Begin
DECLARE @Id int;
DECLARE @InTimeIs varchar(max);
DECLARE @OUTIMEIS varchar(max);

Select top @Id = EmpID From #EmpIdTble

select @InTimeIs = DateTime where empid = @id and StatusINOUT = 1
select @OUTIMEIS = DateTime where empid = @id and StatusINOUT = 2

Insert into #AttendanceTbl values(@Id,@InTimeIs,@OUTIMEIS)

delete #EmpIdTbl where EmpId = @Id
End
-- Displays our final result.
Select * from #AttendanceTbl

If it helps mark as answer. Else come up with your query.
Thank you.

+1