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

Referencia cruzada de la suma de 2 tablas distinta en MySQL

Estas en el tema de Referencia cruzada de la suma de 2 tablas distinta en MySQL en el foro de Mysql en Foros del Web. Necesito Sumar los datos de dos tablas distintas y que el resultado lo muestre en una referencia cruzada. Que la agrupación se dé por Código ...
  #1 (permalink)  
Antiguo 10/04/2014, 22:04
Avatar de hdionicio  
Fecha de Ingreso: enero-2012
Ubicación: Huánuco, Perú
Mensajes: 10
Antigüedad: 12 años, 11 meses
Puntos: 0
Pregunta Referencia cruzada de la suma de 2 tablas distinta en MySQL

Necesito Sumar los datos de dos tablas distintas y que el resultado lo muestre en una referencia cruzada.

Que la agrupación se dé por Código y En la parte horizontal las fechas (que salga la suma de ambas tablas del campo horas)

Tabla 01



Tabla 02



Resultado


Espero me ayuden, desde ya agradezco su apoyo.
  #2 (permalink)  
Antiguo 11/04/2014, 02:17
Colaborador
 
Fecha de Ingreso: marzo-2008
Ubicación: Sabadell
Mensajes: 4.897
Antigüedad: 16 años, 8 meses
Puntos: 574
Respuesta: Referencia cruzada de la suma de 2 tablas distinta en MySQL

MySql no tiene solución para lo que pides....

Código MySQL:
Ver original
  1. SELECT u.codigo,u.fecha,SUM(u.hrs)
  2.    (SELECT codigo,fecha,turno,hrs FROM tabla01
  3.      UNION ALL
  4.     SELECT codigo,fecha,turno,hrs FROM tabla02) u
  5. WHERE <aqui el filtro que quieras>
  6. GROUP BY u.codigo,u.fecha;

Esto te daria el dato base a partir de el yo te aconsejo programación externa, offtopic, para presentar los datos en el formato que deseas.

(la descripción del codigo no se de donde la sacas.... pero seria un problema menor)
__________________
Quim
--------------------------------------------------
Ayudar a ayudar es una buena práctica!!! Y da buenos resultados.
  #3 (permalink)  
Antiguo 03/05/2014, 22:41
Avatar de hdionicio  
Fecha de Ingreso: enero-2012
Ubicación: Huánuco, Perú
Mensajes: 10
Antigüedad: 12 años, 11 meses
Puntos: 0
Respuesta: Referencia cruzada de la suma de 2 tablas distinta en MySQL

Quimfv gracias por responder.

Anexo la tabla Equipamentos

Código MySQL:
Ver original
  1. DROP TABLE IF EXISTS `equipamento`;
  2. CREATE TABLE `equipamento` (
  3.   `equ_codigo` varchar(10) NOT NULL,
  4.   `pro_codigo` varchar(5) NOT NULL,
  5.   `equ_descripcion` varchar(50) NOT NULL,
  6.   `equ_modelo` varchar(30) DEFAULT NULL,
  7.   `equ_serie` varchar(35) DEFAULT NULL,
  8.   `equ_anio_fabricacion` varchar(4) DEFAULT NULL,
  9.   `equ_placa` varchar(10) DEFAULT NULL,
  10.   `equ_efi_mecanica` double NOT NULL,
  11.   `equ_efi_operacional` double NOT NULL,
  12.   `equ_datamob` varchar(10) DEFAULT NULL,
  13.   `equ_datadesmob` varchar(10) DEFAULT NULL,
  14.   `equ_valor_horas` double DEFAULT NULL,
  15.   `equ_horas_minimas` double DEFAULT NULL,
  16.   `cat_codigo` varchar(4) NOT NULL,
  17.   `mar_codigo` varchar(3) NOT NULL,
  18.   `ori_codigo` varchar(2) NOT NULL,
  19.   `fam_codigo` varchar(10) NOT NULL,
  20.   `emp_codigo` varchar(5) NOT NULL,
  21.   `uni_codigo` varchar(5) NOT NULL,
  22.   `hor_codigo` varchar(5) NOT NULL,
  23.   `res_codigo` varchar(10) NOT NULL,
  24.   `sec_codigo` varchar(10) NOT NULL,
  25.   `equ_operador` varchar(2) NOT NULL DEFAULT 'no',
  26.   `estado` varchar(1) NOT NULL,
  27.   `horometrofinal` float NOT NULL,
  28.   PRIMARY KEY (`equ_codigo`,`pro_codigo`),
  29.   KEY `fk_equipamento_categoria` (`cat_codigo`),
  30.   KEY `fk_equipamento_marcas1` (`mar_codigo`),
  31.   KEY `fk_equipamento_origen_equipo1` (`ori_codigo`),
  32.   KEY `fk_equipamento_familia1` (`fam_codigo`),
  33.   KEY `fk_equipamento_empresa1` (`emp_codigo`),
  34.   KEY `fk_equipamento_unidad_medida1` (`uni_codigo`)

Anexo la tabla Horas_paradas

Código MySQL:
Ver original
  1. DROP TABLE IF EXISTS `horas_paradas`;
  2. CREATE TABLE `horas_paradas` (
  3.   `equ_codigo` varchar(10) NOT NULL,
  4.   `tur_codigo` varchar(2) NOT NULL,
  5.   `con_fecha` date NOT NULL,
  6.   `par_codigo` varchar(5) NOT NULL,
  7.   `pro_codigo` varchar(5) NOT NULL,
  8.   `hor_par_hora` double DEFAULT NULL,
  9.   `hor_par_indicador` varchar(3) DEFAULT NULL,
  10.   PRIMARY KEY (`equ_codigo`,`tur_codigo`,`con_fecha`,`par_codigo`,`pro_codigo`),
  11.   KEY `fk_horas_paradas_paralizacion1` (`par_codigo`)

Anexo la tabla Horas_trabajadas

Código MySQL:
Ver original
  1. DROP TABLE IF EXISTS `horas_trabajadas`;
  2. CREATE TABLE `horas_trabajadas` (
  3.   `equ_codigo` varchar(10) NOT NULL,
  4.   `tur_codigo` varchar(2) NOT NULL,
  5.   `con_fecha` date NOT NULL,
  6.   `ua_codigo` varchar(10) NOT NULL,
  7.   `pro_codigo` varchar(5) NOT NULL,
  8.   `hor_tra_horas` double NOT NULL,
  9.   `hor_tra_valor` double DEFAULT NULL,
  10.   `hor_tra_produccion` double DEFAULT NULL,
  11.   PRIMARY KEY (`equ_codigo`,`tur_codigo`,`con_fecha`,`ua_codigo`,`pro_codigo`),
  12.   KEY `fk_horas_trabajadas_ua1` (`ua_codigo`)

Encontré un código algo parecido para un store procedure y me da el resultado pero sólo para una tabla, hay alguna manera de que se pueda unir la otra tabla para que me sume los resultados?

Código MySQL:
Ver original
  1. DROP PROCEDURE IF EXISTS `seguimiento_horas`;
  2. DELIMITER ;;
  3. CREATE DEFINER=`root`@`localhost` PROCEDURE `seguimiento_horas`(in sdate date,in edate date)
  4. declare finish int default 0;
  5. declare cdate date;
  6. declare str varchar(10000) default "select equ_codigo,";
  7. declare curs cursor for select con_fecha from horas_trabajadas where con_fecha between sdate and edate group by con_fecha;
  8. declare continue handler for not found set finish = 1;
  9. open curs;
  10. my_loop:loop
  11. fetch curs into cdate;
  12. if finish = 1 then
  13. leave my_loop;
  14. set str = concat(str, "max(case when con_fecha = '",cdate,"' then hor_tra_horas else null end) as `",cdate,"`,");
  15. end loop;
  16. close curs;
  17. set str = substr(str,1,char_length(str)-1);
  18. set @str = concat(str," from horas_trabajadas
  19.            group by equ_codigo");
  20.  
  21. prepare stmt from @str;
  22. execute stmt;
  23. deallocate prepare stmt;
  24. ;;
  25. DELIMITER ;

Al ejecutar la consulta sale el siguiente resultado.

Código MySQL:
Ver original
  1. mysql> call seguimiento_horas ('2013-04-06','2013-04-12');
  2. +------------+------------+------------+------------+------------+------------+------------+------------+
  3. | equ_codigo | 2013-04-06 | 2013-04-07 | 2013-04-08 | 2013-04-09 | 2013-04-10 | 2013-04-11 | 2013-04-12 |
  4. +------------+------------+------------+------------+------------+------------+------------+------------+
  5. | 01140803   | 2          | 3.75       | NULL       | NULL       | NULL       | NULL       | 1.5        |
  6. | 01140804   | 2.67       | 7          | 2.16       | 4.16       | 5          | NULL       | 6          |
  7. | 01140c01   | 2.8        | 2.5        | 6          | 3.4        | 3.8        | NULL       | 5.5        |
  8. | 01140fe4   | 2          | 4.92       | 6          | NULL       | NULL       | NULL       | 1          |
  9. | 01140fe5   | 6          | 5.75       | 7.6        | 7.7        | 6.5        | NULL       | 7.25       |
  10. | 01143801   | 4          | 3.5        | 6.5        | 7.5        | 3.75       | NULL       | 2.5        |
  11. | 06020p03   | 6          | 6.5        | 6.8        | 5.5        | 7.5        | 3.5        | 4.5        |
  12. | 08007801   | NULL       | 3.5        | 2.8        | 2.7        | 4          | NULL       | 1.5        |
  13. | 08013804   | 8          | 5          | 8.8        | 7          | 1.7        | NULL       | NULL       |
  14. | 08015806   | NULL       | NULL       | NULL       | NULL       | NULL       | NULL       | NULL       |
  15. | 08017tl3   | NULL       | 5          | 6          | 4          | 7          | NULL       | 6.5        |
  16. | 08017tl4   | 5.25       | 3          | 1.75       | 4.25       | 4.25       | NULL       | 1          |
  17. | 08017tl5   | 5.5        | 3          | 4.5        | 3.75       | NULL       | NULL       | NULL       |
  18. | 08065mi1   | 8          | 5.5        | 4.5        | 5          | 4          | NULL       | 6.92       |
  19. | 08065mi2   | 2.67       | 9.16       | 9.16       | 9.16       | 1.67       | NULL       | 6.16       |
  20. | 08065ska   | 3.5        | 5.5        | 8          | 8.5        | 8          | NULL       | 8.5        |
  21. | 09008sk1   | NULL       | NULL       | NULL       | NULL       | NULL       | NULL       | NULL       |
  22. | 09008sk2   | NULL       | NULL       | NULL       | NULL       | NULL       | NULL       | NULL       |
  23. | 09008sk3   | NULL       | NULL       | NULL       | NULL       | NULL       | NULL       | NULL       |
  24. | 09021l05   | 1          | 1          | 2          | 1          | 2          | NULL       | 2          |
  25. | 09038fe1   | 6          | 4.8        | 3          | 5.8        | 3.5        | 10.6       | 3.9        |
  26. | 09062802   | 1.5        | 1          | 7          | 4.5        | 2          | NULL       | 5          |
  27. | 09062up1   | 7.5        | 7.5        | 9          | 6.5        | 8.5        | NULL       | 8.5        |

El detalle es que si, en el procedure pueda sumar las horas de las tablas Horas_paradas + Horas_trabajadas me debería sumar 11.00 por equipo para cada día.

La columna descripción contiene en una tercera tabla equipos.

O en todo caso, como podría unir las tres tablas (Equipamento + Horas_trabajadas + Horas_paradas) y a partir de una vista generar el procedure.

Tengo esta consulta

Código MySQL:
Ver original
  1.     equ_codigo as Codigo
  2.     ,tur_codigo as Turno
  3.     ,con_fecha as Fecha
  4.     ,pro_codigo as CodProyecto
  5.     ,hor_tra_horas as Horas
  6.   FROM horas_trabajadas
  7.     equ_codigo as Codigo
  8.     ,tur_codigo as Turno
  9.     ,con_fecha as Fecha
  10.     ,pro_codigo as CodProyecto
  11.     ,hor_tra_horas as Horas
  12.   FROM horas_paradas
  13.   ORDER BY Codigo

Al ejecutar la consulta me muestra estos valores.

Código MySQL:
Ver original
  1. ---------------------------------------------------------------------
  2. | Codigo     | Turno      | Fecha      | CodProyecto| Horas         |
  3. ---------------------------------------------------------------------
  4. | 64703uq2   | d          | 2013-03-27 | PROIN      | 3.6           |
  5. | 64703uq2   | d          | 2013-04-06 | PROIN      | 6.3           |
  6. | 64703uq2   | d          | 2013-03-27 | PROIN      | 0.25          |
  7. | 64703uq2   | d          | 2013-04-25 | PROIN      | 2             |
  8. | 64703uq2   | d          | 2013-04-23 | PROIN      | 0.25          |
  9. | 64703uq2   | d          | 2013-04-14 | PROIN      | 5.5           |
  10. | 64703uq2   | d          | 2013-04-15 | PROIN      | 3.1           |
  11. | 64703uq2   | d          | 2013-03-30 | PROIN      | 6.7           |
  12. | 64703uq2   | d          | 2013-04-08 | PROIN      | 1             |
  13. | 64703uq2   | d          | 2013-03-28 | PROIN      | 1             |
  14. | 64703uq2   | d          | 2013-04-24 | PROIN      | 3.6           |
  15. | 64703uq2   | d          | 2013-04-16 | PROIN      | 7             |
  16. | 64703uq2   | d          | 2013-04-17 | PROIN      | 8             |
  17. | 64703uq2   | d          | 2013-04-07 | PROIN      | 1             |
  18. | 64703uq2   | d          | 2013-04-10 | PROIN      | 0.25          |
  19. | 64703uq2   | d          | 2013-03-30 | PROIN      | 0.25          |
  20. | 64703uq2   | d          | 2013-04-21 | PROIN      | 1.5           |
  21. | 64703uq2   | d          | 2013-04-20 | PROIN      | 6.2           |
  22. | 64703uq2   | d          | 2013-04-09 | PROIN      | 1             |
  23. | 64703uq2   | d          | 2013-04-12 | PROIN      | 1             |

Al unir las tres tablas me debería mostrar esos campos o alguno adicional, por que la descripción se encuentra en la tabla equipamentos:

------------------------------------------------------------
| Codigo | Descripcion | Turno | Fecha | CodProyecto | Horas |
------------------------------------------------------------

Espero me ayuden.

Gracias de antemano.
  #4 (permalink)  
Antiguo 05/05/2014, 00:36
Colaborador
 
Fecha de Ingreso: marzo-2008
Ubicación: Sabadell
Mensajes: 4.897
Antigüedad: 16 años, 8 meses
Puntos: 574
Respuesta: Referencia cruzada de la suma de 2 tablas distinta en MySQL

Código MySQL:
Ver original
  1. SELECT Sbc.Codigo,
  2.     Eq.equ_descripcion as Descripcion,
  3.     Sbc.Turno,
  4.     Sbc.Fecha,
  5.     Sbc.CodProyecto,
  6.     Sbc.Horas
  7. FROM equipamento Eq
  8.     INNER JOIN (
  9.     SELECT equ_codigo as Codigo,
  10.         tur_codigo as Turno,
  11.         con_fecha as Fecha,
  12.         pro_codigo as CodProyecto,
  13.         hor_tra_horas as Horas
  14.     FROM horas_trabajadas
  15.     UNION ALL
  16.     SELECT equ_codigo as Codigo,
  17.         tur_codigo as Turno,
  18.         con_fecha as Fecha,
  19.         pro_codigo as CodProyecto,
  20.         hor_tra_horas as Horas
  21.     FROM horas_paradas
  22.     ) Sbc
  23.     ON Eq.equ_codigo=Sbc.Codigo
  24. ORDER BY Sbc.Codigo

Ahi se sumarian horas trabajadas y paradas.

Supongo que ya tienes en cuenta que el sistema horario no es decimal.
__________________
Quim
--------------------------------------------------
Ayudar a ayudar es una buena práctica!!! Y da buenos resultados.
  #5 (permalink)  
Antiguo 02/12/2014, 20:08
Avatar de hdionicio  
Fecha de Ingreso: enero-2012
Ubicación: Huánuco, Perú
Mensajes: 10
Antigüedad: 12 años, 11 meses
Puntos: 0
De acuerdo Respuesta: Referencia cruzada de la suma de 2 tablas distinta en MySQL

Logré construir el pivot dinamico, anexo los codigos por si les sirva a alguien.

Código MySQL:
Ver original
  1. DROP TABLE IF EXISTS `horas_paradas`;
  2. CREATE TABLE `horas_paradas` (
  3.   `equ_codigo` varchar(10) NOT NULL,
  4.   `tur_codigo` varchar(2) NOT NULL,
  5.   `con_fecha` date NOT NULL,
  6.   `par_codigo` varchar(5) NOT NULL,
  7.   `pro_codigo` varchar(5) NOT NULL,
  8.   `hor_par_hora` double DEFAULT NULL,
  9.   `hor_par_indicador` varchar(3) DEFAULT NULL,
  10.   PRIMARY KEY (`equ_codigo`,`tur_codigo`,`con_fecha`,`par_codigo`,`pro_codigo`),
  11.   KEY `fk_horas_paradas_paralizacion1` (`par_codigo`)
-----------------------------------------------------------------------
Código MySQL:
Ver original
  1. DROP TABLE IF EXISTS `horas_trabajadas`;
  2. CREATE TABLE `horas_trabajadas` (
  3.   `equ_codigo` varchar(10) NOT NULL,
  4.   `tur_codigo` varchar(2) NOT NULL,
  5.   `con_fecha` date NOT NULL,
  6.   `ua_codigo` varchar(10) NOT NULL,
  7.   `pro_codigo` varchar(5) NOT NULL,
  8.   `hor_tra_horas` double NOT NULL,
  9.   `hor_tra_valor` double DEFAULT NULL,
  10.   `hor_tra_produccion` double DEFAULT NULL,
  11.   PRIMARY KEY (`equ_codigo`,`tur_codigo`,`con_fecha`,`ua_codigo`,`pro_codigo`),
  12.   KEY `fk_horas_trabajadas_ua1` (`ua_codigo`)
-----------------------------------------------------------------------
Uní las 2 tablas mediante este código.

Código MySQL:
Ver original
  1.         e.equ_codigo AS CodigoEQ,
  2. e.equ_descripcion ,'  ',
  3. m.mar_descripcion ,'  ',
  4. e.equ_modelo ,'  ',
  5. empresa.emp_razonsocial) AS DescripcionEQ,
  6. e.equ_modelo AS ModeloEQ,      
  7. ht.tur_codigo AS Turno,
  8.         ht.con_fecha AS Fecha,
  9.         e.pro_codigo AS CodProyecto,
  10.         ht.hor_tra_horas AS Horas,
  11. m.mar_descripcion AS NombreMarca,
  12. empresa.emp_razonsocial AS NombreEmpresa
  13.  
  14. horas_trabajadas ht
  15. INNER JOIN equipamento e
  16. ON ht.equ_codigo = e.equ_codigo
  17. INNER JOIN marcas m
  18. ON e.mar_codigo = m.mar_codigo
  19. INNER JOIN empresa
  20. ON e.emp_codigo = empresa.emp_codigo
  21.  
  22.  
  23.     e.equ_codigo as CodigoEQ,
  24. e.equ_descripcion ,'  ',
  25. m.mar_descripcion ,'  ',
  26. e.equ_modelo ,'  ',
  27. empresa.emp_razonsocial) AS DescripcionEQ,
  28. e.equ_modelo AS ModeloEQ,
  29.     hp.tur_codigo as Turno,
  30.     hp.con_fecha as Fecha,
  31.     e.pro_codigo as CodProyecto,
  32.     hp.hor_par_hora as Horas,
  33. m.mar_descripcion AS NombreMarca,
  34. empresa.emp_razonsocial AS NombreEmpresa
  35.  
  36. FROM horas_paradas hp
  37. INNER JOIN equipamento e
  38. ON hp.equ_codigo = e.equ_codigo
  39. INNER JOIN marcas m
  40. ON e.mar_codigo = m.mar_codigo
  41. INNER JOIN empresa
  42. ON e.emp_codigo = empresa.emp_codigo
  43.  
  44. ORDER BY CodigoEQ
----------------------------------------------------
Luego a partir de la vista construí el Procedimiento almacenado para el pivot dinamico.

Código MySQL:
Ver original
  1. DECLARE finish INT DEFAULT 0;
  2. DECLARE cdate date;
  3. DECLARE str VARCHAR(10000) DEFAULT "select CodigoEQ,DescripcionEQ,Turno,";
  4. DECLARE strfilt VARCHAR(100) DEFAULT CONCAT("FROM res_horas_consolidado WHERE Turno = '",turn,"' GROUP BY CodigoEQ");
  5. DECLARE curs cursor for select Fecha
  6. FROM res_horas_consolidado
  7. WHERE Fecha BETWEEN sdate AND edate GROUP BY Fecha;
  8. DECLARE continue handler for not found SET finish = 1;
  9. OPEN curs;
  10. my_loop:LOOP
  11. FETCH curs INTO cdate;
  12. IF finish = 1 THEN
  13. LEAVE my_loop;
  14. SET str = concat(str, "sum(case when Fecha = '",cdate,"' then Horas else null end) as `",cdate,"`,");
  15. END LOOP;
  16. CLOSE curs;
  17. SET str = substr(str,1,char_length(str)-1);
  18. SET @str = concat(str, strfilt);
  19.  
  20. PREPARE stmt FROM @str;
  21. EXECUTE stmt;
  22. DEALLOCATE PREPARE stmt;

Parametros procedure: IN sdate date, IN edate date, IN turn VARCHAR(10)

Suerte.

Etiquetas: horizontal, pivot, reference, referencia, resultado, suma, tabla, tablas
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 01:17.