How to get the first employee entrance in a day near his shift

Question

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

• AttendanceId (Identity)
• EmployeeId
• Timestamp (in or out card punch)
• AccessCode ( I = IN, O = OUT )

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

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

1. Shift A (From 07:00 to 15:00)
2. Shift B (From 15:00 to 23:00)
3. Shift C (From 23:00 to 07:00 next day)

So what I need is to know for each employee in a selected day (the sample data has just one day and has been filtered to show only IN records, so only IN access to the company) is the first Timestamp that is near (below or above) the Shift entrance.

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

Answer 1

Since you have SQL Server 2012, you can use TimeFromParts and "mod 8" everything to minimize the 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 hour portion of the timestamp "mod 8" to "7" -- that way I don't need to run the query three times.