Referencia cruzada de la suma de 2 tablas distinta en MySQL

hdionicio · #1 (**permalink**) 10/04/2014, 22:04

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.

quimfv · #2 (**permalink**) 11/04/2014, 02:17

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

Código MySQL:

Ver originalSELECT u.codigo,u.fecha,SUM(u.hrs)
FROM 
   (SELECT codigo,fecha,turno,hrs FROM tabla01
     UNION ALL
    SELECT codigo,fecha,turno,hrs FROM tabla02) u
WHERE <aqui el filtro que quieras>
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)

hdionicio · #3 (**permalink**) 03/05/2014, 22:41

Quimfv gracias por responder.

Anexo la tabla Equipamentos

Código MySQL:

Ver originalDROP TABLE IF EXISTS `equipamento`;
CREATE TABLE `equipamento` (
  `equ_codigo` varchar(10) NOT NULL,
  `pro_codigo` varchar(5) NOT NULL,
  `equ_descripcion` varchar(50) NOT NULL,
  `equ_modelo` varchar(30) DEFAULT NULL,
  `equ_serie` varchar(35) DEFAULT NULL,
  `equ_anio_fabricacion` varchar(4) DEFAULT NULL,
  `equ_placa` varchar(10) DEFAULT NULL,
  `equ_efi_mecanica` double NOT NULL,
  `equ_efi_operacional` double NOT NULL,
  `equ_datamob` varchar(10) DEFAULT NULL,
  `equ_datadesmob` varchar(10) DEFAULT NULL,
  `equ_valor_horas` double DEFAULT NULL,
  `equ_horas_minimas` double DEFAULT NULL,
  `cat_codigo` varchar(4) NOT NULL,
  `mar_codigo` varchar(3) NOT NULL,
  `ori_codigo` varchar(2) NOT NULL,
  `fam_codigo` varchar(10) NOT NULL,
  `emp_codigo` varchar(5) NOT NULL,
  `uni_codigo` varchar(5) NOT NULL,
  `hor_codigo` varchar(5) NOT NULL,
  `res_codigo` varchar(10) NOT NULL,
  `sec_codigo` varchar(10) NOT NULL,
  `equ_operador` varchar(2) NOT NULL DEFAULT 'no',
  `estado` varchar(1) NOT NULL,
  `horometrofinal` float NOT NULL,
  PRIMARY KEY (`equ_codigo`,`pro_codigo`),
  KEY `fk_equipamento_categoria` (`cat_codigo`),
  KEY `fk_equipamento_marcas1` (`mar_codigo`),
  KEY `fk_equipamento_origen_equipo1` (`ori_codigo`),
  KEY `fk_equipamento_familia1` (`fam_codigo`),
  KEY `fk_equipamento_empresa1` (`emp_codigo`),
  KEY `fk_equipamento_unidad_medida1` (`uni_codigo`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Anexo la tabla Horas_paradas

Código MySQL:

Ver originalDROP TABLE IF EXISTS `horas_paradas`;
CREATE TABLE `horas_paradas` (
  `equ_codigo` varchar(10) NOT NULL,
  `tur_codigo` varchar(2) NOT NULL,
  `con_fecha` date NOT NULL,
  `par_codigo` varchar(5) NOT NULL,
  `pro_codigo` varchar(5) NOT NULL,
  `hor_par_hora` double DEFAULT NULL,
  `hor_par_indicador` varchar(3) DEFAULT NULL,
  PRIMARY KEY (`equ_codigo`,`tur_codigo`,`con_fecha`,`par_codigo`,`pro_codigo`),
  KEY `fk_horas_paradas_paralizacion1` (`par_codigo`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Anexo la tabla Horas_trabajadas

Código MySQL:

Ver originalDROP TABLE IF EXISTS `horas_trabajadas`;
CREATE TABLE `horas_trabajadas` (
  `equ_codigo` varchar(10) NOT NULL,
  `tur_codigo` varchar(2) NOT NULL,
  `con_fecha` date NOT NULL,
  `ua_codigo` varchar(10) NOT NULL,
  `pro_codigo` varchar(5) NOT NULL,
  `hor_tra_horas` double NOT NULL,
  `hor_tra_valor` double DEFAULT NULL,
  `hor_tra_produccion` double DEFAULT NULL,
  PRIMARY KEY (`equ_codigo`,`tur_codigo`,`con_fecha`,`ua_codigo`,`pro_codigo`),
  KEY `fk_horas_trabajadas_ua1` (`ua_codigo`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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 originalDROP PROCEDURE IF EXISTS `seguimiento_horas`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `seguimiento_horas`(in sdate date,in edate date)
begin
declare finish int default 0;
declare cdate date;
declare str varchar(10000) default "select equ_codigo,";
declare curs cursor for select con_fecha from horas_trabajadas where con_fecha between sdate and edate group by con_fecha;
declare continue handler for not found set finish = 1;
open curs;
my_loop:loop
fetch curs into cdate;
if finish = 1 then
leave my_loop;
end if;
set str = concat(str, "max(case when con_fecha = '",cdate,"' then hor_tra_horas else null end) as `",cdate,"`,");
end loop;
close curs;
set str = substr(str,1,char_length(str)-1);
set @str = concat(str," from horas_trabajadas
            group by equ_codigo");
 
prepare stmt from @str;
execute stmt;
deallocate prepare stmt;
end
;;
DELIMITER ;

Al ejecutar la consulta sale el siguiente resultado.

Código MySQL:

Ver originalmysql> call seguimiento_horas ('2013-04-06','2013-04-12');
+------------+------------+------------+------------+------------+------------+------------+------------+
| equ_codigo | 2013-04-06 | 2013-04-07 | 2013-04-08 | 2013-04-09 | 2013-04-10 | 2013-04-11 | 2013-04-12 |
+------------+------------+------------+------------+------------+------------+------------+------------+
| 01140803   | 2          | 3.75       | NULL       | NULL       | NULL       | NULL       | 1.5        |
| 01140804   | 2.67       | 7          | 2.16       | 4.16       | 5          | NULL       | 6          |
| 01140c01   | 2.8        | 2.5        | 6          | 3.4        | 3.8        | NULL       | 5.5        |
| 01140fe4   | 2          | 4.92       | 6          | NULL       | NULL       | NULL       | 1          |
| 01140fe5   | 6          | 5.75       | 7.6        | 7.7        | 6.5        | NULL       | 7.25       |
| 01143801   | 4          | 3.5        | 6.5        | 7.5        | 3.75       | NULL       | 2.5        |
| 06020p03   | 6          | 6.5        | 6.8        | 5.5        | 7.5        | 3.5        | 4.5        |
| 08007801   | NULL       | 3.5        | 2.8        | 2.7        | 4          | NULL       | 1.5        |
| 08013804   | 8          | 5          | 8.8        | 7          | 1.7        | NULL       | NULL       |
| 08015806   | NULL       | NULL       | NULL       | NULL       | NULL       | NULL       | NULL       |
| 08017tl3   | NULL       | 5          | 6          | 4          | 7          | NULL       | 6.5        |
| 08017tl4   | 5.25       | 3          | 1.75       | 4.25       | 4.25       | NULL       | 1          |
| 08017tl5   | 5.5        | 3          | 4.5        | 3.75       | NULL       | NULL       | NULL       |
| 08065mi1   | 8          | 5.5        | 4.5        | 5          | 4          | NULL       | 6.92       |
| 08065mi2   | 2.67       | 9.16       | 9.16       | 9.16       | 1.67       | NULL       | 6.16       |
| 08065ska   | 3.5        | 5.5        | 8          | 8.5        | 8          | NULL       | 8.5        |
| 09008sk1   | NULL       | NULL       | NULL       | NULL       | NULL       | NULL       | NULL       |
| 09008sk2   | NULL       | NULL       | NULL       | NULL       | NULL       | NULL       | NULL       |
| 09008sk3   | NULL       | NULL       | NULL       | NULL       | NULL       | NULL       | NULL       |
| 09021l05   | 1          | 1          | 2          | 1          | 2          | NULL       | 2          |
| 09038fe1   | 6          | 4.8        | 3          | 5.8        | 3.5        | 10.6       | 3.9        |
| 09062802   | 1.5        | 1          | 7          | 4.5        | 2          | NULL       | 5          |
| 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 originalSELECT
    equ_codigo as Codigo
    ,tur_codigo as Turno
    ,con_fecha as Fecha
    ,pro_codigo as CodProyecto
    ,hor_tra_horas as Horas
  FROM horas_trabajadas
UNION
SELECT
    equ_codigo as Codigo
    ,tur_codigo as Turno
    ,con_fecha as Fecha
    ,pro_codigo as CodProyecto
    ,hor_tra_horas as Horas
  FROM horas_paradas
  ORDER BY Codigo

Al ejecutar la consulta me muestra estos valores.

Código MySQL:

Ver original---------------------------------------------------------------------
| Codigo     | Turno      | Fecha      | CodProyecto| Horas         |
---------------------------------------------------------------------
| 64703uq2   | d          | 2013-03-27 | PROIN      | 3.6           |
| 64703uq2   | d          | 2013-04-06 | PROIN      | 6.3           |
| 64703uq2   | d          | 2013-03-27 | PROIN      | 0.25          |
| 64703uq2   | d          | 2013-04-25 | PROIN      | 2             |
| 64703uq2   | d          | 2013-04-23 | PROIN      | 0.25          |
| 64703uq2   | d          | 2013-04-14 | PROIN      | 5.5           |
| 64703uq2   | d          | 2013-04-15 | PROIN      | 3.1           |
| 64703uq2   | d          | 2013-03-30 | PROIN      | 6.7           |
| 64703uq2   | d          | 2013-04-08 | PROIN      | 1             |
| 64703uq2   | d          | 2013-03-28 | PROIN      | 1             |
| 64703uq2   | d          | 2013-04-24 | PROIN      | 3.6           |
| 64703uq2   | d          | 2013-04-16 | PROIN      | 7             |
| 64703uq2   | d          | 2013-04-17 | PROIN      | 8             |
| 64703uq2   | d          | 2013-04-07 | PROIN      | 1             |
| 64703uq2   | d          | 2013-04-10 | PROIN      | 0.25          |
| 64703uq2   | d          | 2013-03-30 | PROIN      | 0.25          |
| 64703uq2   | d          | 2013-04-21 | PROIN      | 1.5           |
| 64703uq2   | d          | 2013-04-20 | PROIN      | 6.2           |
| 64703uq2   | d          | 2013-04-09 | PROIN      | 1             |
| 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.

quimfv · #4 (**permalink**) 05/05/2014, 00:36

Código MySQL:

Ver originalSELECT Sbc.Codigo,
    Eq.equ_descripcion as Descripcion,
    Sbc.Turno,
    Sbc.Fecha,
    Sbc.CodProyecto,
    Sbc.Horas
FROM equipamento Eq 
    INNER JOIN (
    SELECT equ_codigo as Codigo,
        tur_codigo as Turno,
        con_fecha as Fecha,
        pro_codigo as CodProyecto,
        hor_tra_horas as Horas
    FROM horas_trabajadas
    UNION ALL
    SELECT equ_codigo as Codigo,
        tur_codigo as Turno,
        con_fecha as Fecha,
        pro_codigo as CodProyecto,
        hor_tra_horas as Horas
    FROM horas_paradas
    ) Sbc
    ON Eq.equ_codigo=Sbc.Codigo
ORDER BY Sbc.Codigo

Ahi se sumarian horas trabajadas y paradas.

Supongo que ya tienes en cuenta que el sistema horario no es decimal.

hdionicio · #5 (**permalink**) 02/12/2014, 20:08

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

Código MySQL:

Ver originalDROP TABLE IF EXISTS `horas_paradas`;
CREATE TABLE `horas_paradas` (
  `equ_codigo` varchar(10) NOT NULL,
  `tur_codigo` varchar(2) NOT NULL,
  `con_fecha` date NOT NULL,
  `par_codigo` varchar(5) NOT NULL,
  `pro_codigo` varchar(5) NOT NULL,
  `hor_par_hora` double DEFAULT NULL,
  `hor_par_indicador` varchar(3) DEFAULT NULL,
  PRIMARY KEY (`equ_codigo`,`tur_codigo`,`con_fecha`,`par_codigo`,`pro_codigo`),
  KEY `fk_horas_paradas_paralizacion1` (`par_codigo`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-----------------------------------------------------------------------

Código MySQL:

Ver originalDROP TABLE IF EXISTS `horas_trabajadas`;
CREATE TABLE `horas_trabajadas` (
  `equ_codigo` varchar(10) NOT NULL,
  `tur_codigo` varchar(2) NOT NULL,
  `con_fecha` date NOT NULL,
  `ua_codigo` varchar(10) NOT NULL,
  `pro_codigo` varchar(5) NOT NULL,
  `hor_tra_horas` double NOT NULL,
  `hor_tra_valor` double DEFAULT NULL,
  `hor_tra_produccion` double DEFAULT NULL,
  PRIMARY KEY (`equ_codigo`,`tur_codigo`,`con_fecha`,`ua_codigo`,`pro_codigo`),
  KEY `fk_horas_trabajadas_ua1` (`ua_codigo`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-----------------------------------------------------------------------
Uní las 2 tablas mediante este código.

Código MySQL:

Ver originalSELECT
        e.equ_codigo AS CodigoEQ,
concat(
e.equ_descripcion ,'  ',
m.mar_descripcion ,'  ',
e.equ_modelo ,'  ', 
empresa.emp_razonsocial) AS DescripcionEQ,
e.equ_modelo AS ModeloEQ,       
ht.tur_codigo AS Turno,
        ht.con_fecha AS Fecha,
        e.pro_codigo AS CodProyecto,
        ht.hor_tra_horas AS Horas,
m.mar_descripcion AS NombreMarca,
empresa.emp_razonsocial AS NombreEmpresa
 
FROM
horas_trabajadas ht
INNER JOIN equipamento e
ON ht.equ_codigo = e.equ_codigo
INNER JOIN marcas m
ON e.mar_codigo = m.mar_codigo
INNER JOIN empresa
ON e.emp_codigo = empresa.emp_codigo
 
UNION ALL
 
SELECT
    e.equ_codigo as CodigoEQ,
concat(
e.equ_descripcion ,'  ',
m.mar_descripcion ,'  ',
e.equ_modelo ,'  ', 
empresa.emp_razonsocial) AS DescripcionEQ,
e.equ_modelo AS ModeloEQ,
    hp.tur_codigo as Turno,
    hp.con_fecha as Fecha,
    e.pro_codigo as CodProyecto,
    hp.hor_par_hora as Horas,
m.mar_descripcion AS NombreMarca,
empresa.emp_razonsocial AS NombreEmpresa
 
FROM horas_paradas hp
INNER JOIN equipamento e
ON hp.equ_codigo = e.equ_codigo
INNER JOIN marcas m
ON e.mar_codigo = m.mar_codigo
INNER JOIN empresa
ON e.emp_codigo = empresa.emp_codigo
 
ORDER BY CodigoEQ

----------------------------------------------------
Luego a partir de la vista construí el Procedimiento almacenado para el pivot dinamico.

Código MySQL:

Ver originalBEGIN
DECLARE finish INT DEFAULT 0;
DECLARE cdate date;
DECLARE str VARCHAR(10000) DEFAULT "select CodigoEQ,DescripcionEQ,Turno,";
DECLARE strfilt VARCHAR(100) DEFAULT CONCAT("FROM res_horas_consolidado WHERE Turno = '",turn,"' GROUP BY CodigoEQ");
DECLARE curs cursor for select Fecha 
FROM res_horas_consolidado
WHERE Fecha BETWEEN sdate AND edate GROUP BY Fecha;
DECLARE continue handler for not found SET finish = 1;
OPEN curs;
my_loop:LOOP
FETCH curs INTO cdate;
IF finish = 1 THEN
LEAVE my_loop;
END if;
SET str = concat(str, "sum(case when Fecha = '",cdate,"' then Horas else null end) as `",cdate,"`,");
END LOOP;
CLOSE curs;
SET str = substr(str,1,char_length(str)-1);
SET @str = concat(str, strfilt);
 
PREPARE stmt FROM @str;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END

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

Suerte.