how to get min intime and max outtime from table

Question

how to get min intime and max outtime from table i have a table table1 in that table i have many columns and records like empid empname attendencedate 1 a 04-01-17 08:45am 1 a 04-01-17 09:45am 1 a 04-01-17 10:45am 1 a 04-01-17 12:45pm 1 a 04-01-17 01:45pm 1 a 04-01-17 08:45pm i want only select minimum attendencedate is intime and maximum attendencedate is outtime like below format empid empname intme outtime 1 a 04-01-17 08:45am 04-01-17 08:45pm

Abhinav Abhishek · Answer

declare @table table ( empid int empname varchar(50) attendate datetime ) insert into @table values(1'a''04-01-17 08:45am') insert into @table values(1'a''04-01-17 09:45am') insert into @table values(1'a''04-01-17 10:45am ') insert into @table values(1'a''04-01-17 12:45pm') insert into @table values(1'a''04-01-17 01:45pm') insert into @table values(1'a''04-01-17 08:45pm') --select * from @table select empid empname FORMAT(min(attendate)'MM-dd-yyyy hh:mm tt') as intime FORMAT(max(attendate)'MM-dd-yyyy hh:mm tt') as outtime from @table group by empidempname

Ramesh Barik · Answer

Select Empid empnamemin(attendencedate) as InTime MAX(attendencedate) as OutTime from TblName group by Empidempname