Can anyone help me with the sum of two tables with a dynamic pivot.
tables:
horas_trabajadas
equ_codigo | tur_codigo | con_fecha | hor_tra_horas
horas_paradas
equ_codigo | tur_codigo | con_fecha | hor_par_hora
Add hours to be the same code and corresponding to the same date and shift.
begin
declare finish int default 0;
declare cdate date;
declare str, str2 varchar(10000) default "select DISTINCT equ_codigo,";
declare curs cursor for SELECT DISTINCT equ_codigo FROM horas_trabajadas INNER JOIN horas_paradas ON horas_trabajadas.con_fecha=horas_paradas.con
WHERE horas_trabajadas.equ_codigo=horas_paradas.equ_codigo 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, "sum(case when con_fecha.horas_trabajadas = '",cdate,"' then hor_tra_horas else null end) as `",cdate,"`,");
set str2 = concat(str2, "sum(case when con_fecha.horas_paradas = '",cdate,"' then hor_par_hora else null end) as `",cdate,"`,");
end loop;
close curs;
set str = substr(str,1,char_length(str)-1);
set str2 = substr(str2,1,char_length(str)-1);
set @str = concat(str," from horas_trabajadas
group by equ_codigo");
set @str2 = concat(str2," from horas_paradas
group by equ_codigo");
set @query = concat(str, str2);
prepare stmt from @str;
execute stmt;
deallocate prepare stmt;
end
Thanx.
Submit an article or tip