ASPForums.Net RSS Feedhttp://www.aspforums.net/Handlers/RSS.ashxLatest additions to the content that appears on ASPForums.Net(c) 2019 www.aspforums.com. All rights reserved.Display all Employees In and Out Time of each Day in SQL Server<p>I&nbsp;figure out this approach.</p> <pre class="brush: sql">create table #tempStatus (EmpId int, intTime datetime, sStatus int) insert into #tempStatus values(1, &#39;2018-05-26 08:44&#39;, 1), (1, &#39;2018-05-26 08:44&#39;, 2), (2, &#39;2018-05-28 08:44&#39;, 1), (2, &#39;2018-05-28 12:44&#39;, 2), (1, &#39;2018-05-21 08:44&#39;, 1), (1, &#39;2018-05-21 10:44&#39;, 2), (2, &#39;2018-05-23 08:44&#39;, 1), (2, &#39;2018-05-23 08:44&#39;, 2), (3, &#39;2018-05-23 08:44&#39;, 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 </pre> <p>&nbsp;</p>https://www.aspforums.net:443/Threads/352759/Display-all-Employees-In-and-Out-Time-of-each-Day-in-SQL-Server/https://www.aspforums.net:443/Threads/352759/Display-all-Employees-In-and-Out-Time-of-each-Day-in-SQL-Server/Tue, 18 Dec 2018 00:42:57 GMT