Wie bekomme ich den ersten Mitarbeiter input in einem Tag in der Nähe seiner Schicht

Ich habe eine employee_attendance Tabelle ( SQL server 2012 ) mit den folgenden Spalten:

  • Teilnahme (Identität)
  • Angestellten ID
  • timestempel (in oder aus Kartenstempel)
  • AccessCode (I = IN, O = OUT)

Hier ist die SQL Fiddle mit einigen Beispieldaten: http://sqlfiddle.com/#!3/ba8a1/1

Ich weiß, dass diese Mitarbeiter an jedem Tag auf einer der 3 Schichten arbeiten können, die wir haben:

  1. Shift A (von 07:00 bis 15:00 Uhr)
  2. Umzug B (von 15:00 bis 23:00 Uhr)
  3. Shift C (von 23:00 bis 07:00 am nächsten Tag)

Also, was ich brauche, ist für jeden Mitarbeiter in einem ausgewählten Tag zu wissen (die Beispieldaten haben nur einen Tag und wurde gefiltert, um nur IN- Aufzeichnungen zu zeigen, also nur IN- Zugriff auf das Unternehmen) ist der erste Timestamp , der nahe ist (unten oder oben) der Shift input.

Hier ist ein image von dem, was die SQL Fiddle data nach dem Anwenden des richtigen Skripts aussehen sollten:

Bildbeschreibung hier eingeben

Da du SQL server 2012 hast, kannst du TimeFromParts und "mod 8" alles nutzen, um die Arbeit zu minimieren …

 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 

Also, da sich deine Schicht ändert (7,15,23) sind alle "mod 8 = 7" Ich vergleiche nur den Stundenteil des timestempels "mod 8" bis "7" – so muss ich nicht laufen Abfrage dreimal.

SQLFiddle (danke für die Bereitstellung von Beispieldaten): http://sqlfiddle.com/#!3/ba8a1/7/0

Abfrage:

 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 

Erläuterung:

  1. Erstellen Sie für jeden Mitarbeiter eine Zeile für jede Schichtstartzeit.
  2. Berechnen Sie die Differenz zwischen der aktuellen Startzeit und der angezeigten Uhrzeit. Nehmen Sie absoluten Wert, weil die ursprüngliche Anfrage sucht die nächste Uhr-in, nicht unbedingt vor dem Shift begann.
  3. Verwenden Sie row_number, um die Zeile für jeden Mitarbeiter mit der kürzesten Differenz zu einer Shift-Startzeit zu identifizieren.
  4. Gib nur die nächste Zeile zur Schaltstartzeit zurück.

Wie andere festgestellt haben, geht das davon aus, dass ein Mitarbeiter nur eine Schicht pro Tag hat und dass die nächstgelegene Uhrzeit der Schaltzeit diejenige ist, die gültig ist. Für ein einzelnes Schicht pro Tag System könnte es sinnvoller sein, mit alternativen Kriterien zu kommen – zum Beispiel beim Betrachten der absolut frühesten Uhrzeit oder der frühesten Uhr in einem gegebenen windows (sagen wir 6:30 bis 7: 30, für die 7:00 Schicht), aber das ist nicht, was dieser Antragsteller gefragt hat.