CODEWITHSUNDEEP
sql-server
Doonie Darkoo
Doonie Darkoo

Reputation: 1495

First TimeIn Last TimeOut for each employee for respective days

I have following table from which I want to extract the time calculated. I am looking to get the Hours Spent by each employee for each day.

CREATE TABLE Attendance 
(
     , EmpID INT
     , TimeIn datetime
     , TimeOut datetime
)

The sample record against this table I have is listed below.

 EmpID | AttendanceTimeIN  |  AttendanceTimeOut
 1       2017-04-01 9:00:00   2017-04-01 10:20:00
 2       2017-04-01 9:00:00   2017-04-01 12:30:00 
 1       2017-04-01 10:25:00  2017-04-01 17:30:00 
 2       2017-04-01 13:26:00  2017-04-01 14:50:00 
 2       2017-04-01 15:00:00  2017-04-01 18:00:00 
 1       2017-04-02 9:00:00   2017-04-02 11:00:00 
 1       2017-04-02 11:10:00  2017-04-02 12:00:00 
 2       2017-04-02 9:00:00   2017-04-02 12:00:00 
 1       2017-04-02 12:50:00  2017-04-02 18:00:00 
 2       2017-04-02 12:51:00  2017-04-02 18:00:00

I want to get the First TimeIn and Last TimeOut of and employee for each day to calculate how many hours a specific employee have spent in office each day. I'm bit confused that how to use Min/Max function so I can get both employees hours for each day. The result set I am looking for should look like this.

 EmpID | AttendanceTimeIN  |  AttendanceTimeOut
 1       2017-04-01 9:00:00   2017-04-01 17:30:00
 2       2017-04-01 9:00:00   2017-04-01 18:00:00 
 1       2017-04-02 9:00:00   2017-04-02 18:00:00 
 2       2017-04-02 9:00:00   2017-04-02 18:00:00

Any help would be highly appreciated. Thank you

Upvotes: 1

Views: 409

Answers (1)

Kris Lawton
Kris Lawton

Reputation: 169

If your TimeIn and TimeOut are datetime type (which they should be!), this solution works with the tests I did:

SELECT
    EmpID
    , MIN(TimeIn)
    , MAX(TimeOut)
FROM Attendance
GROUP BY EmpID, CAST(TimeIn AS DATE)

the GROUP BY clause means that there's one row for each employee and each day, since CASTing to DATE gets rid of the time part. MIN and MAX then just inherently work.

Upvotes: 1

Related Questions