Foros del Web » Programación para mayores de 30 ;) » Bases de Datos General » Mysql »

[SOLUCIONADO] select case suma incorrecta

Estas en el tema de select case suma incorrecta en el foro de Mysql en Foros del Web. Hola de nuevo. Tengo esta nueva consulta: @import url("http://static.forosdelweb.com/clientscript/vbulletin_css/geshi.css"); Código MySQL: Ver original select personal_asistencia.id_personal as ID , concat ( nombre , ' ' , ...
  #1 (permalink)  
Antiguo 15/02/2018, 09:48
 
Fecha de Ingreso: marzo-2010
Ubicación: Buenos Aires
Mensajes: 58
Antigüedad: 9 años, 7 meses
Puntos: 2
select case suma incorrecta

Hola de nuevo. Tengo esta nueva consulta:
Código MySQL:
Ver original
  1. select personal_asistencia.id_personal as ID, concat(nombre, ' ', apellido) as Nombre,
  2.  
  3.     case
  4.       when (salida >= '2018-01-01 00:00:00' and salida <= '2018-01-31 23:59:59')
  5. then time_format(sec_to_time(sum(timestampdiff(second, entrada, salida))), '%H:%i')
  6.       when (salida > '2018-01-31 23:59:59')
  7. then time_format(sec_to_time(sum(timestampdiff(second, entrada, '2018-01-31 23:59:59'))), '%H:%i')
  8.       when (salida = 0)
  9.     end as 'Horas totales',
  10.  
  11.     precio_hora as 'Precio/Hora'
  12.     from personal_asistencia
  13.     inner join personal on personal.id = personal_asistencia.id_personal
  14.     inner join personal_precio_hora on personal.id = personal_precio_hora.id_personal
  15.     where (entrada between '2018-01-01 00:00:00' and '2018-01-31 23:59:59')
  16.     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!

Última edición por 7sistemas; 15/02/2018 a las 10:49
  #2 (permalink)  
Antiguo 15/02/2018, 11:01
Avatar de Libras
Colaborador
 
Fecha de Ingreso: agosto-2006
Ubicación: En la hermosa perla de occidente
Mensajes: 7.408
Antigüedad: 13 años, 3 meses
Puntos: 774
Respuesta: select case suma incorrecta

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
__________________
What does an execution plan say to t-sql query? Go f**k yourself, if you are not happy with me
  #3 (permalink)  
Antiguo 16/02/2018, 04:54
 
Fecha de Ingreso: marzo-2010
Ubicación: Buenos Aires
Mensajes: 58
Antigüedad: 9 años, 7 meses
Puntos: 2
Respuesta: select case suma incorrecta

Si si, sé cuáles son las condiciones. Lo que no sé es por qué la suma no es correcta.
  #4 (permalink)  
Antiguo 16/02/2018, 08:43
Avatar de Libras
Colaborador
 
Fecha de Ingreso: agosto-2006
Ubicación: En la hermosa perla de occidente
Mensajes: 7.408
Antigüedad: 13 años, 3 meses
Puntos: 774
Respuesta: select case suma incorrecta

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.
__________________
What does an execution plan say to t-sql query? Go f**k yourself, if you are not happy with me
  #5 (permalink)  
Antiguo 19/02/2018, 04:10
 
Fecha de Ingreso: marzo-2010
Ubicación: Buenos Aires
Mensajes: 58
Antigüedad: 9 años, 7 meses
Puntos: 2
Respuesta: select case suma incorrecta

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:
Ver original
  1. +----+-------------+---------------------+---------------------+-----------+-------------+
  2. | id | id_personal | entrada             | salida              | id_estado | comentarios |
  3. +----+-------------+---------------------+---------------------+-----------+-------------+
  4. |  1 |           2 | 2018-01-01 08:00:00 | 2018-01-01 10:00:00 |      NULL | NULL        |
  5. |  2 |           2 | 2018-01-02 09:00:00 | 2018-01-02 10:00:00 |      NULL | NULL        |
  6. |  3 |           5 | 2018-01-02 20:00:00 | 2018-01-03 20:00:00 |      NULL | NULL        |
  7. |  4 |           5 | 2018-01-04 20:00:00 | 2018-01-05 20:00:00 |      NULL | NULL        |
  8. |  5 |           5 | 2018-01-31 20:00:00 | 2018-02-01 20:00:00 |      NULL | NULL        |
  9. |  6 |           2 | 2018-02-02 09:00:00 | 2018-02-02 10:00:00 |      NULL | NULL        |
  10. |  7 |           2 | 2018-02-03 09:00:00 | 2018-02-03 10:00:00 |      NULL | NULL        |
  11. |  8 |           5 | 2018-02-04 20:00:00 | 0000-00-00 00:00:00 |      NULL | NULL        |
  12. |  9 |           3 | 2018-01-31 20:00:00 | 0000-00-00 00:00:00 |      NULL | NULL        |
  13. +----+-------------+---------------------+---------------------+-----------+-------------+


Código MySQL:
Ver original
  1. set @iniciomes = '2018-02-01 00:00:00';
  2. set @finmes = '2018-02-28 23:59:59';
  3.  
  4. select personal_asistencia.id_personal, monthname(@iniciomes) as Mes,
  5.   # entrada en el mes en curso  
  6.   when ((entrada >= @iniciomes) and (entrada <= @finmes))
  7.     then
  8.       case
  9.     # salida en el mes en curso
  10.     when ((salida != '0000-00-00 00:00:00') and (salida >= @iniciomes) and (salida <= @finmes))
  11.     then time_format(sec_to_time(sum(timestampdiff(second, entrada, salida))), '%H:%i')
  12.     # salida en el siguiente mes
  13. **  when ((salida != 0) and (salida > @finmes))
  14.     then time_format(sec_to_time(sum(timestampdiff(second, entrada, @finmes))), '%H:%i')
  15.     # sin salir
  16.     when (salida = '0000-00-00 00:00:00')
  17.     then time_format(sec_to_time(sum(timestampdiff(second, entrada, now()))), '%H:%i')
  18.       end
  19.  
  20.   # entrada en el mes anterior
  21.   when (entrada < @iniciomes)
  22.   then
  23.     case
  24.       # salida en el mes en curso
  25.       when ((salida != '0000-00-00 00:00:00') and (salida >= @iniciomes) and (salida <= @finmes))
  26.       then time_format(sec_to_time(sum(timestampdiff(second, @iniciomes, salida))), '%H:%i')
  27.       # sin salir
  28.       when (salida = '0000-00-00 00:00:00')
  29.       then time_format(sec_to_time(sum(timestampdiff(second, @iniciomes, now()))), '%H:%i')
  30.     end
  31.   # si no se cumple ninguna, suma cero
  32. #  else time_format(sec_to_time(sum(timestampdiff(second, @iniciomes, @iniciomes))), '%H:%i')
  33.  
  34. end as Horas
  35. from personal_asistencia
  36.   when ((entrada >= @iniciomes) and (entrada <= @finmes))
  37.   then
  38.     case
  39.       when ((salida != '0000-00-00 00:00:00') and (salida >= @iniciomes) and (salida <= @finmes))
  40.       then (entrada >= @iniciomes) and (entrada <= @finmes) and (salida != '0000-00-00 00:00:00') and (salida >= @iniciomes) and (salida <= @finmes)
  41.      
  42. **      when ((salida != 0) and (salida > @finmes))
  43.       then (entrada >= @iniciomes) and (entrada <= @finmes) and
  44. (salida = (select salida from personal_asistencia where entrada >= @iniciomes and entrada <= @finmes and salida > @finmes limit 1))
  45.      
  46.       when (salida = '0000-00-00 00:00:00')
  47.       then (entrada >= @iniciomes) and (entrada <= @finmes) and (salida = '0000-00-00 00:00:00')
  48.     end
  49.  
  50.     when (entrada < @iniciomes)
  51.     then
  52.       case
  53.     when ((salida != '0000-00-00 00:00:00') and (salida >= @iniciomes) and (salida <= @finmes))
  54.     then (entrada < @iniciomes) and (salida != '0000-00-00 00:00:00') and (salida >= @iniciomes) and (salida <= @finmes)
  55.  
  56.     when (salida = '0000-00-00 00:00:00')
  57.     then (entrada < @iniciomes) and (salida = '0000-00-00 00:00:00')
  58.       end
  59. group by id_personal


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


Código MySQL:
Ver original
  1. set @iniciomes = '2018-01-01 00:00:00';
  2. set @finmes = '2018-01-31 23:59:59';
  3. set @cero = '0000-00-00 00:00:00';
  4.  
  5. select personal_asistencia.id_personal, monthname(@iniciomes) as Mes,
  6.   # entrada en el mes en curso  
  7.   when ((entrada >= @iniciomes) and (entrada <= @finmes))
  8.     then
  9.       case
  10.     # salida en el siguiente mes
  11.     when ((salida != @cero) and (salida > @finmes))
  12.     then time_format(sec_to_time(sum(timestampdiff(second, entrada, @finmes))), '%H:%i')
  13.       end
  14. end as Horas
  15. from personal_asistencia
  16.   when ((entrada >= @iniciomes) and (entrada <= @finmes))
  17.   then
  18.     case
  19.       when ((salida != @cero) and (salida > @finmes))
  20.       then (entrada >= @iniciomes) and (entrada <= @finmes) and (salida = (select salida from personal_asistencia where entrada >= @iniciomes and entrada <= @finmes and salida > @finmes limit 1))
  21.     end
  22. group by id_personal

Última edición por 7sistemas; 19/02/2018 a las 07:09 Razón: Añadí nueva consulta.
  #6 (permalink)  
Antiguo 20/02/2018, 12:55
Avatar de mortiprogramador
Colaborador
 
Fecha de Ingreso: septiembre-2009
Ubicación: mortuoria
Mensajes: 3.805
Antigüedad: 10 años, 2 meses
Puntos: 214
Respuesta: select case suma incorrecta

Saludo


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


Código MySQL:
Ver original
  1. pa.id_personal ID, CONCAT(nombre, ' ', apellido) Nombre, SUM(pa.Horas) 'Horas totales', precio_hora 'Precio/Hora'
  2. (
  3.     SELECT
  4.     id_personal,
  5.     CASE
  6.         WHEN (salida >= '2018-01-01 00:00:00' AND salida <= '2018-01-31 23:59:59')
  7.             THEN time_format(sec_to_time(timestampdiff(second, entrada, salida)), '%H:%i')
  8.           WHEN (salida > '2018-01-31 23:59:59')
  9.             THEN time_format(sec_to_time(timestampdiff(second, entrada, '2018-02-01 00:00:00')), '%H:%i')
  10.           WHEN (salida = 0 )
  11.             THEN time_format(sec_to_time(timestampdiff(second, entrada, now())), '%H:%i')
  12.     END Horas
  13.         FROM personal_asistencia
  14.     WHERE datetime1 BETWEEN '2018-01-01 00:00:00' AND '2018-01-31 23:59:59'
  15. ) pa
  16. INNER JOIN personal ON pa.id_personal = personal.id
  17. INNER JOIN personal_precio_hora ON personal.id = personal_precio_hora.id_personal
  18. GROUP BY pa.id_personal;
__________________
"Si consigues ser algo más que un hombre, si te entregas a un ideal, si nadie puede detenerte, te conviertes en algo muy diferente."
Visita piggypon.com
  #7 (permalink)  
Antiguo 20/02/2018, 18:02
 
Fecha de Ingreso: marzo-2010
Ubicación: Buenos Aires
Mensajes: 58
Antigüedad: 9 años, 7 meses
Puntos: 2
Respuesta: select case suma incorrecta

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

Gracias!
  #8 (permalink)  
Antiguo 22/02/2018, 04:12
 
Fecha de Ingreso: marzo-2010
Ubicación: Buenos Aires
Mensajes: 58
Antigüedad: 9 años, 7 meses
Puntos: 2
Respuesta: select case suma incorrecta

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:
Ver original
  1. set @iniciomes = '2018-02-01 00:00:00';
  2. set @finmes = '2018-02-28 23:59:59';
  3. set @cero = '0000-00-00 00:00:00';
  4.  
  5. select pa.id_personal ID, monthname(@iniciomes) as Mes, time_format(sec_to_time(SUM(pa.Horas)), '%H:%i') 'Horas totales',
  6. precio_hora 'Precio/Hora'
  7.   select id_personal,
  8.     case
  9.       # entrada en el mes en curso  
  10.       when ((entrada >= @iniciomes) and (entrada <= @finmes))
  11.     then
  12.       case
  13.         # salida en el mes en curso
  14.         when ((salida != @cero) and (salida >= @iniciomes) and (salida <= @finmes))
  15.         then timestampdiff(second, entrada, salida)
  16.         # salida en el siguiente mes
  17.         when ((salida != @cero) and (salida > @finmes))
  18.         then timestampdiff(second, entrada, @finmes)
  19.         # sin salir, en el siguiente mes
  20.         when ((salida = @cero) and (now() > @finmes))
  21.         then timestampdiff(second, entrada, @finmes)
  22.         #sin salir, todavia en el mes
  23.         when ((salida = @cero) and (now() < @finmes))
  24.         then timestampdiff(second, entrada, now())
  25.       end
  26.  
  27.       # entrada en el mes anterior
  28.       when (entrada < @iniciomes)
  29.       then
  30.     case
  31.       # salida en el mes en curso
  32.       when ((salida != @cero) and (salida >= @iniciomes) and (salida <= @finmes))
  33.       then timestampdiff(second, @iniciomes, salida)
  34.       # sin salir
  35.       when (salida = @cero)
  36.       then timestampdiff(second, @iniciomes, now())
  37.     end
  38.  
  39.     end as Horas
  40.     from personal_asistencia
  41.     where
  42.     case
  43.       when ((entrada >= @iniciomes) and (entrada <= @finmes))
  44.       then
  45.     case
  46.       when ((salida != @cero) and (salida >= @iniciomes) and (salida <= @finmes))
  47.       then (entrada >= @iniciomes) and (entrada <= @finmes) and (salida != @cero) and (salida >= @iniciomes) and (salida <= @finmes)
  48.      
  49.       when ((salida != @cero) and (salida > @finmes))
  50.       then (entrada >= @iniciomes) and (entrada <= @finmes) and
  51. (salida = (select salida from personal_asistencia where entrada >= @iniciomes and entrada <= @finmes and salida > @finmes limit 1))
  52.      
  53.       when ((salida = @cero) and (now() > @finmes))
  54.       then (entrada >= @iniciomes) and (entrada <= @finmes) and (salida = @cero)
  55.      
  56.       when ((salida = @cero) and (now() < @finmes))
  57.       then (entrada >= @iniciomes) and (entrada <= @finmes) and (salida = @cero)
  58.     end
  59.  
  60.     when (entrada < @iniciomes)
  61.     then
  62.       case
  63.         when ((salida != @cero) and (salida >= @iniciomes) and (salida <= @finmes))
  64.         then (entrada < @iniciomes) and (salida != @cero) and (salida >= @iniciomes) and (salida <= @finmes)
  65.  
  66.         when (salida = @cero)
  67.         then (entrada < @iniciomes) and (salida = @cero)
  68.       end
  69.     end
  70. ) as pa
  71.  
  72. INNER JOIN personal ON pa.id_personal = personal.id
  73. INNER JOIN personal_precio_hora ON personal.id = personal_precio_hora.id_personal
  74. GROUP BY pa.id_personal;
  #9 (permalink)  
Antiguo 22/02/2018, 12:50
 
Fecha de Ingreso: marzo-2010
Ubicación: Buenos Aires
Mensajes: 58
Antigüedad: 9 años, 7 meses
Puntos: 2
Respuesta: select case suma incorrecta

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

!
  #10 (permalink)  
Antiguo 23/02/2018, 09:48
 
Fecha de Ingreso: marzo-2010
Ubicación: Buenos Aires
Mensajes: 58
Antigüedad: 9 años, 7 meses
Puntos: 2
Respuesta: select case suma incorrecta

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


Código MySQL:
Ver original
  1. #set @iniciomes = '2018-01-01 00:00:00';
  2. #set @finmes = '2018-02-01 00:00:00';
  3. set @iniciomes = '2018-02-01 00:00:00';
  4. set @finmes = '2018-03-01 00:00:00';
  5. set @cero = '0000-00-00 00:00:00';
  6.  
  7. drop table if exists informe_horas_persona;
  8. create temporary table informe_horas_persona(
  9.   HorasTotales time not null default '00:00:00',
  10.   Pago float(9, 2) not null default 0.00
  11.   ) as
  12.   select personal_asistencia.id_personal, concat(nombre, ' ', apellido) as Nombre, entrada, salida, precio_hora
  13.   from personal_asistencia
  14.   inner join personal on personal.id = personal_asistencia.id_personal
  15.   inner join personal_precio_hora on personal_precio_hora.id_personal = personal_asistencia.id_personal
  16.   where (entrada between @iniciomes and @finmes)
  17. or ((entrada < @iniciomes) and (salida between @iniciomes and @finmes))
  18. or ((entrada < @finmes) and (salida > @finmes))
  19. ;
  20.  
  21. alter table informe_horas_persona modify column precio_hora float(7,2);
  22.  
  23. update informe_horas_persona set HorasTotales = (
  24.  
  25.   case
  26.     when ((entrada between @iniciomes and @finmes) and (salida between @iniciomes and @finmes))
  27.     then time_format(sec_to_time(timestampdiff(second, entrada, salida)), '%H:%i')
  28.    
  29.     when ((entrada between @iniciomes and @finmes) and (salida > @finmes))
  30.     then time_format(sec_to_time(timestampdiff(second, entrada, @finmes)), '%H:%i')
  31.    
  32.     when ((entrada < @iniciomes) and (salida between @iniciomes and @finmes))
  33.     then time_format(sec_to_time(timestampdiff(second, @iniciomes, salida)), '%H:%i')
  34.    
  35.     when ((entrada < @iniciomes) and (salida = @cero))
  36.     then time_format(sec_to_time(timestampdiff(second, @iniciomes, now())), '%H:%i')
  37.    
  38.     when ((entrada between @iniciomes and @finmes) and (salida = @cero))
  39.     then time_format(sec_to_time(timestampdiff(second, entrada, now())), '%H:%i')
  40.   end
  41. )
  42. ;
  43.  
  44. update informe_horas_persona set Pago = (
  45. round(((time_format(HorasTotales, '%H:%i')*60 + minute(HorasTotales))) * (precio_hora/60), 2)
  46. )
  47. ;
  48.  
  49. select * from informe_horas_persona;

Última edición por 7sistemas; 23/02/2018 a las 09:51 Razón: Corregir saltos de línea

Etiquetas: case, select, suma
Atención: Estás leyendo un tema que no tiene actividad desde hace más de 6 MESES, te recomendamos abrir un Nuevo tema en lugar de responder al actual.
Respuesta




La zona horaria es GMT -6. Ahora son las 13:32.