Page Comparison

declare v_issuetype varchar(255);
	declare v_create varchar(4000);
	declare v_select varchar(4000);
	declare v_i int;
	declare v_alias char(2);
	declare v_rownum int;
	declare v_j int;
	declare v_tipoProyecto smallint;

drop table if exists activity;
drop table if exists rankgroup;
drop table if exists ranks;
drop table if exists b0;
drop table if exists b1;
drop table if exists b2;
drop table if exists b3;
drop table if exists b4;

select pj.tipo_proyecto into @v_tipoProyecto from Proyectos_Issue_Tracker pj where pj.IssueTrackerProject = p_proyecto;

create temporary table rankgroup
select ji.proyecto, ji.tipo, count(1) items
from RELEASE_INFO ji, Proyectos_Issue_Tracker pj
where ((p_group = 1) OR (p_group = 0 and ji.proyecto = ifnull(p_proyecto,ji.proyecto)))
and (ji.proyecto = pj.IssueTrackerProject AND (pj.tipo_proyecto = if(p_proyecto is null, pj.tipo_proyecto, @v_tipoProyecto)))
group by ji.proyecto, ji.tipo;

set @rownum := 0;
create temporary table ranks
SELECT g.proyecto, g.tipo, g.items, (@rownum := @rownum + 1) as rank 
FROM rankgroup g 
ORDER BY g.items DESC;

create temporary table activity
select rm.proyecto, 	
	concat(convert(year(rm.fecha_release), char), " Quarter , convert(quarter(rm.fecha_release), char)) as quarter,
	rk.tipo,
	count(1) total
from RELEASE_INFO ji, release_measures rm, ranks rk
where ji.proyecto = rk.proyecto and ji.tipo = rk.tipo
	and ji.proyecto = rm.proyecto and ji.nombre_release = rm.nombre_release
	and rk.rank <= 5
group by rm.proyecto, 	
	concat(convert(year(rm.fecha_release), char), " Quarter ", convert(quarter(rm.fecha_release), char)),
	rk.tipo;

create temporary table totalActivity
select rm.proyecto, 	
	concat(convert(year(rm.fecha_release), char), " Quarter ", convert(quarter(rm.fecha_release), char)) as quarter,
	rk.tipo,
	count(1) total
from RELEASE_INFO ji, release_measures rm, ranks rk
where ji.proyecto = rk.proyecto and ji.tipo = rk.tipo
	and ji.proyecto = rm.proyecto and ji.nombre_release = rm.nombre_release
group by rm.proyecto, 	
	concat(convert(year(rm.fecha_release), char), " Quarter ", convert(quarter(rm.fecha_release), char)),
	rk.tipo;


BEGIN

DECLARE done TINYINT DEFAULT 0;
DECLARE issuetypes_cursor CURSOR FOR select distinct tipo from activity order by tipo;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

OPEN issuetypes_cursor;

set @v_create = ""; 
set @v_select = "select b.proyecto, b.quarter,";
set @v_i = 0;

FETCH NEXT FROM issuetypes_cursor INTO v_issuetype;

read_loop: LOOP

	IF done THEN
      		LEAVE read_loop;
    	END IF;

	set @v_alias = concat("b", convert(@v_i,char));

	set @v_create = concat("create temporary table ", @v_alias, 
		" select proyecto, quarter, sum(total) as total from activity where tipo =",
		char(34), v_issuetype, char(34),
		" group by proyecto, quarter; ");

	PREPARE stmt FROM @v_create;
	EXECUTE stmt;
	DEALLOCATE PREPARE stmt;


	set @v_select = concat(@v_select, "ifnull((select ",
		@v_alias, ".total from ", @v_alias, 
		" where ", @v_alias, ".proyecto=b.proyecto and ",
		@v_alias, ".quarter=b.quarter),0) as " char(34), v_issuetype, char(34), ", ");

	FETCH NEXT FROM issuetypes_cursor INTO v_issuetype;

	set @v_i = @v_i + 1;

END LOOP;

CLOSE issuetypes_cursor;

-- completa campos siempre devuelve 5 grupos
set @v_j = @v_i;
j_loop: LOOP
	if @v_j = 5 then
		leave j_loop;
	end if;
	set @v_select = concat(@v_select, "0 as dummy", @v_j - @v_i, ",");
	set @v_j = @v_j + 1; 
END LOOP;

set @v_select = concat(@v_select," sum(total) as total from totalActivity b group by b.proyecto, b.quarter ORDER BY b.proyecto, b.quarter;");

PREPARE stmt FROM @v_select;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END;

drop table activity;
drop table totalActivity;