sql-server-2008-r2 – MIN() and MAX() to set an employee's work entry and exit time

Question:

My problem is presented in this way: I have a table with all the employee clocks, they have the userid and checktime parameters (includes date and time), all this data is stored in the form of a list.

Example:

userid      checktime
1           2021-04-30 19:25:27.000
1           2021-05-03 09:05:29.000
2           2021-05-03 12:59:34.000
2           2021-05-03 12:59:35.000
3           2021-05-03 13:00:01.000
3           2021-05-03 14:28:12.000
[...]

My objective is to take the markings of the work entry time and work exit time linearly for each job by date. And in case there is no check-out time record (either because the employee forgot to clock out), display the field as 00:00:00 or null

example:

userid     fecha        entrada     salida
---------------------------------------------
1          2021/04/30   19:25:27    00:00:00
2          2021/05/03   07:30:00    18:00:00
3          2021/05/04   07:30:00    18:00:00
4          2021/05/05   07:30:00    18:00:00
5          2021/05/06   09:30:00    00:00:00

The way I did it is the following:

SELECT USERID,CHECKTIME, MIN(CONVERT(CHAR(8), CHECKTIME, 108)) 'MIN', MAX(CONVERT(CHAR(8), CHECKTIME, 108)) 'MAX' FROM dbo.marcaciones GROUP BY USERID, CHECKTIME ORDER BY USERID;

And I haven't been able to do it. Can you help me?

Answer:

First of all, due to your comment, we will ignore the cases of night hours that require another type of somewhat more complex solution, even so there are some problems that do not have a clear solution:

  1. A single registration in the day, is it an entry or an exit?
  2. Only 2 consecutive records, seconds apart for input or output

First we get the minimum and maximum value per user and date:

SELECT USERID,
       CAST(CHECKTIME AS DATE) as Fecha, 
       MIN(CONVERT(CHAR(8), CHECKTIME, 108)) as 'entrada', 
       MAX(CONVERT(CHAR(8), CHECKTIME, 108)) as 'salida',
       FROM dbo.marcaciones 
       GROUP BY USERID, CAST(CHECKTIME AS DATE)

In a subquery we will try to solve the previous questions

SELECT T.USERID,
       T.Fecha,
       T.entrada,
       CASE WHEN DATEDIFF(ss, T.salida, T.entrada) <= 5 THEN NULL ELSE T.salida END as 'salida'
       FROM (SELECT USERID,
                    CAST(CHECKTIME AS DATE) as Fecha, 
                    MIN(CONVERT(CHAR(8), CHECKTIME, 108)) as 'entrada', 
                    MAX(CONVERT(CHAR(8), CHECKTIME, 108)) as 'salida',
                    FROM dbo.marcaciones 
                    GROUP BY USERID, CAST(CHECKTIME AS DATE)
       ) T
       ORDER BY T.USERID, T.Fecha

Finally with this we get that: 1) A single record in the day is considered to be an entry (eventually it could be seen if it is greater than a certain time to consider it an exit) 2) If there are only two consecutive records of up to 5 seconds apart , or a single record, is considered as input only and the output will be NULL.

This is just basic, the management of schedules is a complex issue, for example this scheme does not allow you to determine absences (days without registration)

Post Views: 2
Scroll to Top