Check hour of entry and exit of employee night shift in sql

Question

Check hour of entry and exit of employee night shift in sql

Navigation

#1 by (1 votes)

1

I appeal to you for support and suggestions. I have the following table in SQL:

It is the record of entries and exits of employees that keeps a clock, as you will see it is not possible to differentiate inputs from outputs since everything is inserted as a column. I have no problem with getting the time of entry and exit of those employees who are NOT assigned a night shift, since I am using a select min and max in the hour field and grouping by date and employee ID. Where I have a problem is with those employees who have assigned a night shift: / since I do not know how to select your time of entry and exit as it registers entry one day (15-12-2017 22:00) and leaves the next day (16- 12-2017 6:00), it is even possible that by mistake check twice either in and out, in this case I occupy all the way to display min and max time. Any suggestion of what you can do about it? What I occupy is to obtain an exit like this:

The query that I am currently using is:

SELECT MIN(Hora) as HoraEntrada,MAX(Hora)AS HoraSalida,Fecha,IDEmpleado, IdTurno, TurnoNocturno FROM RegistroEmpleados GROUP BY IDEmpleado,Fecha,IdTurno,TurnoNocturno

But I get this output:

I would greatly appreciate your help.

Greetings!

sql sql-server

asked by Jonsin 23.12.2017 в 00:16

source

1 answer

Command equivalent to 'hostname' in Windows Delete rungs from a Python csv

score 1 · Accepted Answer

What you could do is establish a rule that, when it is a night shift, then if the departure time is less than noon, then the date is grouped with the previous day.

Assuming that the columns Fecha and Hora are of type DateTime , the query could be something like this:

SELECT MIN(Hora) as HoraEntrada,
       MAX(Hora)AS HoraSalida,
       CASE WHEN TurnoNocturno = 1 AND DATEPART(HOUR, Hora) < 12 THEN Fecha - 1 ELSE Fecha END AS Fecha,
       IDEmpleado, 
       IdTurno, 
       TurnoNocturno
  FROM RegistroEmpleados
 GROUP BY IDEmpleado,
          CASE WHEN TurnoNocturno = 1 AND DATEPART(HOUR, Hora) < 12 THEN Fecha - 1 ELSE Fecha END,
          IdTurno,
          TurnoNocturno

Obviously, you can adjust the logic to what you want. But the idea is that you have some logic that allows you to determine when the date should be taken into account as if it were the day before.