First-In and Last-Out times for Night Shift Employees

Question

I can get FirstIn - LastOut out of Day shift employee using min and max:

select 
    UserId,
    [date], 
    min(convert(char(5), [Login], 108)) as FirstIN,
    max(convert(char(5), LogOut,108)) as LastOUT
from Employee
group by UserId, [date]

But I cannot do for Night Shift employee, like 5:30 Pm to next day Morning 2:30 am. At the same day (Next day) they will come again on 5:30 pm.

Shift Table

UserID   ShiftName      Start   End     
2267     Night Shift-1  17:30   02:30 

Employee Table

UserID LogIn                    LogOut                    LogDate
2267   2016-01-04 20:52:08.000  2016-01-04 22:09:22.000   2016-01-04 00:00:00.000
2267   2016-01-04 23:00:07.000  2016-01-04 23:00:07.000   2016-01-04 00:00:00.000
2267   2016-01-05 00:35:46.000  2016-01-05 00:35:46.000   2016-01-05 00:00:00.000
2267   2016-01-05 01:02:31.000  2016-01-05 03:57:16.000   2016-01-05 00:00:00.000
2267   2016-01-05 18:43:50.000  2016-01-05 19:05:04.000   2016-01-05 00:00:00.000
2267   2016-01-05 19:10:20.000  2016-01-05 22:26:00.000   2016-01-05 00:00:00.000
2267   2016-01-05 23:27:24.000  2016-01-05 23:27:24.000   2016-01-05 00:00:00.000
2267   2016-01-06 03:45:16.000  2016-01-06 03:45:16.000   2016-01-06 00:00:00.000

Desired output

EmpId   date           FirstIN    LastOUT
2267    2016-01-04     20:52      03:57
2267    2016-01-05     18:43      03:45 

Answer 1

This query shifts the LogIn date by the number of minutes between midnight (0=00:00) and the start of the shift:

• 17:30 (Start) becomes 00:00
• 2:30 becomes 9:00.
• 2016-01-06 03:45:16.0000000 becomes 2016-01-05 10:15:16.0000000
• ...

It is used to get the date of the shift and it can then be used in a GROUP BY.

Query:

WITH s AS (
    SELECT s.UserID, e.LogIn, e.LogOut
        , [DATE] = CAST(DATEADD(minute, DATEDIFF(minute, s.[Start], 0), e.LogIn) as date)
    FROM @Shifts s
    INNER JOIN @Employees e ON s.UserID = e.UserID 
)
SELECT s.UserID, [DATE]
    , FirstIN = CAST(MIN(LogIn) as time)
    , LastOut = CAST(MAX(LogOut) as time)
FROM s
GROUP BY s.UserID, [DATE];

See SQL Fiddle.

Output:

UserID  DATE        FirstIN             LastOut
2267    2016-01-04  20:52:08.0000000    03:57:16.0000000
2267    2016-01-05  18:43:50.0000000    03:45:16.0000000

Arrive early, leave late:

If someone is leaving a little late, it should work fine.

If someone arrive early, you can add some time to DATEADD such as:

[DATE] = CAST(DATEADD(minute, 90 + DATEDIFF(minute, s.[Start], 0), e.LogIn) as date)

Here it adds 90 minutes. This means that if someone arrives up to 90 minutes early it will be counted on the current day. It will be for LogIn starting at 16:00 with your sample (17:30 - 90mins) If an employee arrives 2h early (> 90min), it is counted as the previous day.

Working hours:

The number of hours between FirstIn and LastOut in hours is calculated like this:

DATEDIFF(hour, MIN(LogIn), MAX(LogOut))

Or like this:

DATEADD(minute, DATEDIFF(minute, MIN(LogIn), MAX(LogOut)), CAST('' as time))

Working hours output:

UserID  DATE        FirstIN             LastOut             WorkHours   WorkTime
2267    2016-01-04  20:52:08.0000000    03:57:16.0000000    7           07:05:00.0000000
2267    2016-01-05  18:43:50.0000000    03:45:16.0000000    9           09:02:00.0000000