How to get the first working entrance a day near his shift

Question

I have an employee_attendance table ( SQL Server 2012 ) with the following columns:

• AttendanceId (Identity)
• EmployeeId
• Time stamp (to or from a card punch)
• AccessCode (I = IN, O = OUT)

Here is a SQL Fiddle with some sample data: http://sqlfiddle.com/#!3/ba8a1/1

I know that these employees can work any day on any of the three shifts that we have:

• Shift A (from 07:00 to 15:00)
• Shift B (from 15:00 to 23:00)
• Shift C (from 23:00 to 07:00 the next day)

So I need to know for each employee on the selected day (sample data has only one day and was filtered to display only IN records, therefore only IN strong access to the company) - this is the first Timestamp , which is located near (below or above) the Shift input .

Here is an image of what the SQL Fiddle data should look like after applying the correct script:

Answer 1

Since you have SQL Server 2012, you can use TimeFromParts and "mod 8" to minimize work ...

 select employeeId, accessCode, minDiff = MIN(ABS(DATEDIFF(TIMEFROMPARTS(DATEPART(HH,t.timestamp) % 8, (DATEPART(MI,t.timestamp), (DATEPART(S,t.timestamp),0,0), TIMEFROMPARTS(7, (DATEPART(MI,t.timestamp), (DATEPART(S,t.timestamp)) from table t were t.timestamp is in a given daily range 

So, since your shift changes (7,15,23) are all “mod 8 = 7”, I just compare the hourly part of the “8” timestamp with “7” - this way I don't need to run the request three times.

Answer 2

SQLFiddle (thanks for providing sample data): http://sqlfiddle.com/#!3/ba8a1/7/0

Query:

 declare @day datetime = '5-20-15' --not necessary, but included for more flexibility; you can just hardcode the date below if you want SELECT *, '7:00:00 shift' as Shift , abs(datediff(minute, timestamp, @day + '7:00:00')) as TimeDiff into #temp FROM employee_attendance union all SELECT *, '15:00:00 shift' , abs(datediff(minute, timestamp, @day + '15:00:00')) FROM employee_attendance union all SELECT *, '23:00:00 shift' , abs(datediff(minute, timestamp, @day + '23:00:00')) FROM employee_attendance select * from ( select * , row_number() over (partition by EmployeeID order by TimeDiff) as RowN from #temp ) a where RowN = 1 

Explanation:

• Create a line for each employee, for each shift start time.
• Calculate the difference between the actual start time and the indicated start time. Take the absolute value, because the original request is looking for the nearest synchronizer, not necessarily before the start of the shift.
• Use row_number to define a row for each employee with the minimum difference in switch start time.
• Returns only the closest line to the start time of the shift.

As others noted, this suggests that the worker has only one shift per day and that the one closest to the shift time is the one that is valid. For a system with one shift per day, it may make more sense to resort to alternative criteria - for example, looking at the earliest hours-hours or the earliest hours in a given window (say, from 6:30 to 7:30, for a shift of 7:00) but this is not what this request requested.