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

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


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

StackExchange Database Administrators Q#127715, answer score: 6