SQL Server 2012 – First-In and Last-Out Times for Night Shift Employees

sql serversql-server-2012

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

Best Answer

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

Related Solutions

Sql-server – About employee who come out at night

If I correct understand Your question this should work. I have provided a link to SQL FIDDLE:

declare @max_date datetime
declare @min_date datetime

select @min_date = '2015-10-01 07:00:00'
select @max_date = '2015-10-31 05:00:00'

SELECT EmployeeId
    , (SELECT TOP 1 CAST(CAST(CONVERT(DATE, CheckTime) AS VARCHAR(10)) + ' ' + CAST(MIN(CAST(CheckTime AS TIME)) AS VARCHAR(8)) AS DATETIME) FROM table_name AS t2 WHERE t2.EmployeeId = t1.EmployeeId GROUP By CheckTime ORDER BY CAST(CheckTime AS TIME) ) AS  MinDateTime
    , (SELECT TOP 1 CAST(CAST(CONVERT(DATE, CheckTime) AS VARCHAR(10)) + ' ' + CAST(MIN(CAST(CheckTime AS TIME)) AS VARCHAR(8)) AS DATETIME) FROM table_name AS t2 WHERE t2.EmployeeId = t1.EmployeeId GROUP By CheckTime ORDER BY CAST(CheckTime AS TIME) DESC ) AS MaxDateTime 
FROM table_name AS t1
WHERE CheckTime BETWEEN @min_date and @max_date GROUP BY EmployeeId;

SQL Fiddle - http://sqlfiddle.com/#!6/fd199/2 DATA:

1   2015-10-14 18:06:22
1   2015-10-28 23:06:40
2   2015-10-07 18:13:58
2   2015-10-14 11:14:15
1   2015-10-14 19:20:11
2   2015-10-08 10:20:34
3   2015-10-01 20:25:28
3   2015-10-30 10:26:04

RESULT:

1   2015-10-14 18:06:22.000 2015-10-28 23:06:40.000
2   2015-10-08 10:20:34.000 2015-10-07 18:13:58.000
3   2015-10-30 10:26:04.000 2015-10-01 20:25:28.000

SQL Server 2012 – Versions of FIRST() and LAST() Functions

You were close - FIRST and LAST are from Access; in SQL Server (starting with SQL Server 2012) they are FIRST_VALUE() and LAST_VALUE().

So, if you are 2012 or better (or Azure SQL Database), here's one way to get your answer:

CREATE TABLE #fl
(
  IdentityColumn INT IDENTITY, 
  Value INT
);

INSERT #fl(Value) SELECT 10;
INSERT #fl(Value) SELECT 45;
INSERT #fl(Value) SELECT 65;
INSERT #fl(Value) SELECT 95;
INSERT #fl(Value) SELECT 200;

SELECT TOP (1) LAST_VALUE(Value) OVER (ORDER BY IdentityColumn)
            - FIRST_VALUE(Value) OVER (ORDER BY IdentityColumn)
  FROM #fl
  ORDER BY IdentityColumn DESC;

GO
DROP TABLE #fl;