[SOLUCIONADO] select case suma incorrecta

[7sistemas]

Hola de nuevo. Tengo esta nueva consulta:

Código MySQL:

select personal_asistencia.id_personal as ID, concat(nombre, ' ', apellido) as Nombre,
 
    case
      when (salida >= '2018-01-01 00:00:00' and salida <= '2018-01-31 23:59:59') 
then time_format(sec_to_time(sum(timestampdiff(second, entrada, salida))), '%H:%i')
      when (salida > '2018-01-31 23:59:59') 
then time_format(sec_to_time(sum(timestampdiff(second, entrada, '2018-01-31 23:59:59'))), '%H:%i')
      when (salida = 0) 
then time_format(sec_to_time(sum(timestampdiff(second, entrada, now()))), '%H:%i')
    end as 'Horas totales',
 
    precio_hora as 'Precio/Hora'
    from personal_asistencia
    inner join personal on personal.id = personal_asistencia.id_personal
    inner join personal_precio_hora on personal.id = personal_precio_hora.id_personal 
    where (entrada between '2018-01-01 00:00:00' and '2018-01-31 23:59:59')
    group by Nombre
 

que devuelve esto:

Código:

+----+-----------------+---------------+-------------+
| ID | Nombre          | Horas totales | Precio/Hora |
+----+-----------------+---------------+-------------+
|  2 | Nom Apellido 1  | 03:00         |        95.5 |
|  5 | Nom Apellido 2  | 72:00         |          80 |
+----+-----------------+---------------+-------------+

Para Nombre Apellido 2, las Horas totales deberían ser 52. Sin embargo, está sumando también las horas del mes siguiente.

Alguna idea?

Gracias de antemano!

[Libras]

Si tienes 3 condiciones, una es cuando esta en el mes y la va a sumar, otra cuando es mayor al mes y la va a sumar, y la tercera cuando es igual y la va a sumar sobre el mismo campo

[7sistemas]

Si si, sé cuáles son las condiciones. Lo que no sé es por qué la suma no es correcta.

[Libras]

No entendiste vdd?? Al decirte que tienes 3 condiciones, quiere decir que si tienes digamos 20 para la condicion uno 10 para la condicion 2 y 5 para la condicion 3 el resultado seria 35, porque las 3 condiciones se cumplen, ahora si nada mas quiere que el resultado sea digamos 25, podrias quitar una condicion o cambiarla para que se ajuste a lo que necesitas.

[7sistemas]

Cómo se van a cumplir las tres condiciones para un registro? La fecha de salida lo restringe. Se supone que el case, cuando encuentra la primera coincidencia, la ejecuta y pasa al siguiente registro.

La cuestión es que reescribí la consulta completa. Probé cada suma con su correspondiente where. Por separado todas funcionan bien. Cuando las meto en el case, falla la condición marcada con "**", que es la condición en que la salida es en el siguiente mes. Dejo la consulta y la tabla de donde salen los datos.

Código MySQL:

+----+-------------+---------------------+---------------------+-----------+-------------+
| id | id_personal | entrada             | salida              | id_estado | comentarios |
+----+-------------+---------------------+---------------------+-----------+-------------+
|  1 |           2 | 2018-01-01 08:00:00 | 2018-01-01 10:00:00 |      NULL | NULL        |
|  2 |           2 | 2018-01-02 09:00:00 | 2018-01-02 10:00:00 |      NULL | NULL        |
|  3 |           5 | 2018-01-02 20:00:00 | 2018-01-03 20:00:00 |      NULL | NULL        |
|  4 |           5 | 2018-01-04 20:00:00 | 2018-01-05 20:00:00 |      NULL | NULL        |
|  5 |           5 | 2018-01-31 20:00:00 | 2018-02-01 20:00:00 |      NULL | NULL        |
|  6 |           2 | 2018-02-02 09:00:00 | 2018-02-02 10:00:00 |      NULL | NULL        |
|  7 |           2 | 2018-02-03 09:00:00 | 2018-02-03 10:00:00 |      NULL | NULL        |
|  8 |           5 | 2018-02-04 20:00:00 | 0000-00-00 00:00:00 |      NULL | NULL        |
|  9 |           3 | 2018-01-31 20:00:00 | 0000-00-00 00:00:00 |      NULL | NULL        |
+----+-------------+---------------------+---------------------+-----------+-------------+
 

Código MySQL:

set @iniciomes = '2018-02-01 00:00:00';
set @finmes = '2018-02-28 23:59:59';
 
select personal_asistencia.id_personal, monthname(@iniciomes) as Mes, 
case
  # entrada en el mes en curso  
  when ((entrada >= @iniciomes) and (entrada <= @finmes)) 
    then
      case
    # salida en el mes en curso
    when ((salida != '0000-00-00 00:00:00') and (salida >= @iniciomes) and (salida <= @finmes))
    then time_format(sec_to_time(sum(timestampdiff(second, entrada, salida))), '%H:%i')
    # salida en el siguiente mes
**  when ((salida != 0) and (salida > @finmes)) 
    then time_format(sec_to_time(sum(timestampdiff(second, entrada, @finmes))), '%H:%i')
    # sin salir
    when (salida = '0000-00-00 00:00:00') 
    then time_format(sec_to_time(sum(timestampdiff(second, entrada, now()))), '%H:%i')
      end
 
  # entrada en el mes anterior
  when (entrada < @iniciomes) 
  then
    case
      # salida en el mes en curso
      when ((salida != '0000-00-00 00:00:00') and (salida >= @iniciomes) and (salida <= @finmes))
      then time_format(sec_to_time(sum(timestampdiff(second, @iniciomes, salida))), '%H:%i')
      # sin salir
      when (salida = '0000-00-00 00:00:00')
      then time_format(sec_to_time(sum(timestampdiff(second, @iniciomes, now()))), '%H:%i')
    end
  # si no se cumple ninguna, suma cero
#  else time_format(sec_to_time(sum(timestampdiff(second, @iniciomes, @iniciomes))), '%H:%i')
 
end as Horas
from personal_asistencia
where
case
  when ((entrada >= @iniciomes) and (entrada <= @finmes)) 
  then
    case
      when ((salida != '0000-00-00 00:00:00') and (salida >= @iniciomes) and (salida <= @finmes))
      then (entrada >= @iniciomes) and (entrada <= @finmes) and (salida != '0000-00-00 00:00:00') and (salida >= @iniciomes) and (salida <= @finmes)
      
**      when ((salida != 0) and (salida > @finmes)) 
      then (entrada >= @iniciomes) and (entrada <= @finmes) and 
(salida = (select salida from personal_asistencia where entrada >= @iniciomes and entrada <= @finmes and salida > @finmes limit 1))
      
      when (salida = '0000-00-00 00:00:00')
      then (entrada >= @iniciomes) and (entrada <= @finmes) and (salida = '0000-00-00 00:00:00')
    end
 
    when (entrada < @iniciomes) 
    then
      case
    when ((salida != '0000-00-00 00:00:00') and (salida >= @iniciomes) and (salida <= @finmes))
    then (entrada < @iniciomes) and (salida != '0000-00-00 00:00:00') and (salida >= @iniciomes) and (salida <= @finmes)
 
    when (salida = '0000-00-00 00:00:00')
    then (entrada < @iniciomes) and (salida = '0000-00-00 00:00:00')
      end
end
group by id_personal
 

Sin embargo, esto funciona bien. Es la misma consulta, sin el resto de condiciones.

Código MySQL:

set @iniciomes = '2018-01-01 00:00:00';
set @finmes = '2018-01-31 23:59:59';
set @cero = '0000-00-00 00:00:00';
 
select personal_asistencia.id_personal, monthname(@iniciomes) as Mes, 
case
  # entrada en el mes en curso  
  when ((entrada >= @iniciomes) and (entrada <= @finmes)) 
    then
      case
    # salida en el siguiente mes
    when ((salida != @cero) and (salida > @finmes)) 
    then time_format(sec_to_time(sum(timestampdiff(second, entrada, @finmes))), '%H:%i')
      end
end as Horas
from personal_asistencia
where
case
  when ((entrada >= @iniciomes) and (entrada <= @finmes)) 
  then
    case
      when ((salida != @cero) and (salida > @finmes)) 
      then (entrada >= @iniciomes) and (entrada <= @finmes) and (salida = (select salida from personal_asistencia where entrada >= @iniciomes and entrada <= @finmes and salida > @finmes limit 1))
    end
end
group by id_personal
 

[mortiprogramador]

Saludo


Pues yo propongo una subconsulta sobre la consulta inicial,
es decir, algo así:

Código MySQL:

SELECT 
pa.id_personal ID, CONCAT(nombre, ' ', apellido) Nombre, SUM(pa.Horas) 'Horas totales', precio_hora 'Precio/Hora'
FROM 
(
    SELECT 
    id_personal, 
    CASE
        WHEN (salida >= '2018-01-01 00:00:00' AND salida <= '2018-01-31 23:59:59') 
            THEN time_format(sec_to_time(timestampdiff(second, entrada, salida)), '%H:%i')
          WHEN (salida > '2018-01-31 23:59:59') 
            THEN time_format(sec_to_time(timestampdiff(second, entrada, '2018-02-01 00:00:00')), '%H:%i')
          WHEN (salida = 0 ) 
            THEN time_format(sec_to_time(timestampdiff(second, entrada, now())), '%H:%i')
    END Horas
        FROM personal_asistencia
    WHERE datetime1 BETWEEN '2018-01-01 00:00:00' AND '2018-01-31 23:59:59'
) pa
INNER JOIN personal ON pa.id_personal = personal.id
INNER JOIN personal_precio_hora ON personal.id = personal_precio_hora.id_personal
GROUP BY pa.id_personal;
 

[7sistemas]

Ajá! Voy a hacer las pruebas y ajustes y vuelvo en unos días con las conclusiones.

Gracias!

[7sistemas]

Finalmente, encontré la consulta que devuelve los resultados correctos.
Me percaté de una condición que no había contemplado: cuando la entrada fue en el mes en curso, hay que verificar si ahora (el momento del calculo) es mayor o menor al mes solicitado.

De todas maneras, debo hacer mas pruebas para verificar al 100% la consulta.

Gracias Libras y Mortiprogramador.

Esta es la consulta, y la tabla, la misma.

Código MySQL:

set @iniciomes = '2018-02-01 00:00:00';
set @finmes = '2018-02-28 23:59:59';
set @cero = '0000-00-00 00:00:00';
 
select pa.id_personal ID, monthname(@iniciomes) as Mes, time_format(sec_to_time(SUM(pa.Horas)), '%H:%i') 'Horas totales', 
precio_hora 'Precio/Hora'
from (
  select id_personal, 
    case
      # entrada en el mes en curso  
      when ((entrada >= @iniciomes) and (entrada <= @finmes)) 
    then
      case
        # salida en el mes en curso
        when ((salida != @cero) and (salida >= @iniciomes) and (salida <= @finmes))
        then timestampdiff(second, entrada, salida)
        # salida en el siguiente mes
        when ((salida != @cero) and (salida > @finmes)) 
        then timestampdiff(second, entrada, @finmes)
        # sin salir, en el siguiente mes
        when ((salida = @cero) and (now() > @finmes))
        then timestampdiff(second, entrada, @finmes)
        #sin salir, todavia en el mes
        when ((salida = @cero) and (now() < @finmes))
        then timestampdiff(second, entrada, now())
      end
 
      # entrada en el mes anterior
      when (entrada < @iniciomes) 
      then
    case
      # salida en el mes en curso
      when ((salida != @cero) and (salida >= @iniciomes) and (salida <= @finmes))
      then timestampdiff(second, @iniciomes, salida)
      # sin salir
      when (salida = @cero)
      then timestampdiff(second, @iniciomes, now())
    end
 
    end as Horas
    from personal_asistencia
    where
    case
      when ((entrada >= @iniciomes) and (entrada <= @finmes)) 
      then
    case
      when ((salida != @cero) and (salida >= @iniciomes) and (salida <= @finmes))
      then (entrada >= @iniciomes) and (entrada <= @finmes) and (salida != @cero) and (salida >= @iniciomes) and (salida <= @finmes)
      
      when ((salida != @cero) and (salida > @finmes)) 
      then (entrada >= @iniciomes) and (entrada <= @finmes) and 
(salida = (select salida from personal_asistencia where entrada >= @iniciomes and entrada <= @finmes and salida > @finmes limit 1))
      
      when ((salida = @cero) and (now() > @finmes))
      then (entrada >= @iniciomes) and (entrada <= @finmes) and (salida = @cero)
      
      when ((salida = @cero) and (now() < @finmes))
      then (entrada >= @iniciomes) and (entrada <= @finmes) and (salida = @cero)
    end
 
    when (entrada < @iniciomes) 
    then
      case
        when ((salida != @cero) and (salida >= @iniciomes) and (salida <= @finmes))
        then (entrada < @iniciomes) and (salida != @cero) and (salida >= @iniciomes) and (salida <= @finmes)
 
        when (salida = @cero)
        then (entrada < @iniciomes) and (salida = @cero)
      end
    end
) as pa
 
INNER JOIN personal ON pa.id_personal = personal.id
INNER JOIN personal_precio_hora ON personal.id = personal_precio_hora.id_personal
GROUP BY pa.id_personal;
 

[7sistemas]

Bueno, no está solucionado. Resulta que cuando se da la condición de que una persona entra en un mes y sale en el siguiente, no suma las horas del mes en que entró.

Estoy buscando una alternativa con una tabla temporal.
Cuando lo tenga encaminado, posteo nuevamente.

Saludos

!

[7sistemas]

Finalmente lo resolví usando tablas temporales. Esto es lo que hice.
(lo lógico sería ponerlo en un procedimiento, no?)

Código MySQL:

#set @iniciomes = '2018-01-01 00:00:00';
#set @finmes = '2018-02-01 00:00:00';
set @iniciomes = '2018-02-01 00:00:00';
set @finmes = '2018-03-01 00:00:00';
set @cero = '0000-00-00 00:00:00';
 
drop table if exists informe_horas_persona;
create temporary table informe_horas_persona(
  HorasTotales time not null default '00:00:00',
  Pago float(9, 2) not null default 0.00
  ) as
  select personal_asistencia.id_personal, concat(nombre, ' ', apellido) as Nombre, entrada, salida, precio_hora
  from personal_asistencia
  inner join personal on personal.id = personal_asistencia.id_personal
  inner join personal_precio_hora on personal_precio_hora.id_personal = personal_asistencia.id_personal
  where (entrada between @iniciomes and @finmes) 
or ((entrada < @iniciomes) and (salida between @iniciomes and @finmes)) 
or ((entrada < @finmes) and (salida > @finmes))
;
 
alter table informe_horas_persona modify column precio_hora float(7,2);
 
update informe_horas_persona set HorasTotales = (
 
select 
  case
    when ((entrada between @iniciomes and @finmes) and (salida between @iniciomes and @finmes))
    then time_format(sec_to_time(timestampdiff(second, entrada, salida)), '%H:%i')
    
    when ((entrada between @iniciomes and @finmes) and (salida > @finmes))
    then time_format(sec_to_time(timestampdiff(second, entrada, @finmes)), '%H:%i')
    
    when ((entrada < @iniciomes) and (salida between @iniciomes and @finmes))
    then time_format(sec_to_time(timestampdiff(second, @iniciomes, salida)), '%H:%i')
    
    when ((entrada < @iniciomes) and (salida = @cero))
    then time_format(sec_to_time(timestampdiff(second, @iniciomes, now())), '%H:%i')
    
    when ((entrada between @iniciomes and @finmes) and (salida = @cero))
    then time_format(sec_to_time(timestampdiff(second, entrada, now())), '%H:%i')
  end
)
;
 
update informe_horas_persona set Pago = (
round(((time_format(HorasTotales, '%H:%i')*60 + minute(HorasTotales))) * (precio_hora/60), 2)
)
;
 
select * from informe_horas_persona;