Separat data in column SttusINOUT wise into two columns

Question

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

Suraj Kumar · 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

Suraj Kumar · Answer

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.

Suraj Kumar · Answer

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 Hop e this is the correct and final output as expected.

Akhter HUssain · Answer

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

Akhter HUssain · Answer

i have just one table here you are using three table

Akhter HUssain · Answer

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

Akhter HUssain · Answer

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

Sreekanth Reddy · Answer

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.

Akhter HUssain · Answer

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)

Suraj Kumar · Answer

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);

Suraj Kumar · Answer

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.

Akhter HUssain · Answer

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

Akhter HUssain · Answer

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

Akhter HUssain · Answer

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

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

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

Separat data in column SttusINOUT wise into two columns

Answers (14)