how to get min intime and max outtime from table

siva nathan

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

2 Replies

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

Abhinav Abhishek9y ago
Accepted 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 empid,empname
Ramesh Barik9y ago
Select Empid, empname,min(attendencedate) as InTime, MAX(attendencedate) as OutTime from TblName group by Empid,empname