Pivot table Procs

pivot_table.sql

delimiter //
drop procedure if exists dynamic_view2//
create procedure dynamic_view2(in userid int, in sdate date,in edate date)
begin
declare finish int default 0;
declare cdate date;
declare str varchar(10000) default "select task_name,sub_task_name, ";
declare curs cursor for select creation_date from task where creation_date between sdate and edate group by creation_date;
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, "SEC_TO_TIME(SUM(TIME_TO_SEC(case when creation_date = '",cdate,"' then hours else 0 end))) as `",cdate,"`,");
end loop;
close curs;
set str = substr(str,1,char_length(str)-1);
set @str = concat(str," from task where user_id=",userid,"
            group by task_name,sub_task_name");

prepare stmt from @str;
execute stmt;
deallocate prepare stmt;
end;//
delimiter ;

call dynamic_view2(14,'2014-12-01','2014-12-22');


delimiter //
drop procedure if exists dynamic_view2_all//
create procedure dynamic_view2_all(in sdate date,in edate date)
begin
declare finish int default 0;
declare cdate date;
declare str varchar(10000) default "select task_name,sub_task_name,get_user_name(user_id), ";
declare curs cursor for select creation_date from task where creation_date between sdate and edate group by creation_date;
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, "SEC_TO_TIME(SUM(TIME_TO_SEC(case when creation_date = '",cdate,"' then hours else 0 end))) as `",cdate,"`,");
end loop;
close curs;
set str = substr(str,1,char_length(str)-1);
set @str = concat(str," from task where task_name <> ''
            group by task_name,sub_task_name,user_id");

prepare stmt from @str;
execute stmt;
deallocate prepare stmt;
end;//
delimiter ;

call dynamic_view2_all('2014-12-01','2014-12-22');


select task_name,sub_task_name,hours,creation_date from task where user_id=14 and category='task';
 select task_name,sub_task_name,hours,creation_date,id from task where user_id=14 and category='task' and task_name='GE Setup :: US-NGS Server';



=============

delimiter //
drop procedure if exists dynamic_view2_category//
create procedure dynamic_view2_category(in userid int, in sdate date,in edate date)
begin
declare finish int default 0;
declare cdate date;
declare str varchar(10000) default "select category,description, ";
declare curs cursor for select creation_date from task where creation_date between sdate and edate group by creation_date;
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, "SEC_TO_TIME(SUM(TIME_TO_SEC(case when creation_date = '",cdate,"' then hours else 0 end))) as `",cdate,"`,");
end loop;
close curs;
set str = substr(str,1,char_length(str)-1);
set @str = concat(str," from task where user_id=",userid,"
            group by category,description");

prepare stmt from @str;
execute stmt;
deallocate prepare stmt;
end;//
delimiter ;

call dynamic_view2_category(14,'2014-12-01','2014-12-22');


==================

delimiter //
drop procedure if exists dynamic_view2_category_all//
create procedure dynamic_view2_category_all(in sdate date,in edate date)
begin
declare finish int default 0;
declare cdate date;
declare str varchar(10000) default "select get_user_name(user_id),category, ";
declare curs cursor for select creation_date from task where creation_date between sdate and edate group by creation_date;
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, "SEC_TO_TIME(SUM(TIME_TO_SEC(case when creation_date = '",cdate,"' then hours else 0 end))) as `",cdate,"`,");
end loop;
close curs;
set str = substr(str,1,char_length(str)-1);
set @str = concat(str," from task
            group by category,user_id");

prepare stmt from @str;
execute stmt;
deallocate prepare stmt;
end;//
delimiter ;

call dynamic_view2_category_all('2014-12-01','2014-12-22');


==================


http://stackoverflow.com/questions/6158230/mysql-pivot-table-with-dynamic-headers-based-on-single-column-data





delimiter //
drop procedure if exists dynamic_view2_sp_tasks//
create procedure dynamic_view2_sp_tasks(in tid double, in sdate date,in edate date)
begin
declare finish int default 0;
declare cdate date;
declare str varchar(10000) default "select task_name,sub_task_name,get_user_name(user_id), ";
declare curs cursor for select creation_date from task where creation_date between sdate and edate group by creation_date;
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, "SEC_TO_TIME(SUM(TIME_TO_SEC(case when creation_date = '",cdate,"' then hours else 0 end))) as `",cdate,"`,");
end loop;
close curs;
set str = substr(str,1,char_length(str)-1);
set @str = concat(str," from task where task_id =",tid,"
            group by task_name,sub_task_name,user_id");

prepare stmt from @str;
execute stmt;
deallocate prepare stmt;
end;//
delimiter ;

call dynamic_view2_sp_tasks(21659167624364,'2014-12-01','2014-12-22');


======================


delimiter //
drop procedure if exists dynamic_view2_all_users//
create procedure dynamic_view2_all_users(in sdate date,in edate date)
begin
declare finish int default 0;
declare cdate date;
declare str varchar(10000) default "select get_user_name(user_id), ";
declare curs cursor for select creation_date from task where creation_date between sdate and edate group by creation_date;
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, "SEC_TO_TIME(SUM(TIME_TO_SEC(case when creation_date = '",cdate,"' then hours else 0 end))) as `",cdate,"`,");
end loop;
close curs;

set str = substr(str,1,char_length(str)-1);
set @str = concat(str," from task
            group by user_id");

prepare stmt from @str;
execute stmt;
deallocate prepare stmt;
end;//
delimiter ;

call dynamic_view2_all_users('2014-12-01','2014-12-22');



======================
select full_name from users where user_id not in (select user_id from task where creation_date = '2014-12-22' group by user_id,creation_date order by creation_date);
select full_name from users where user_id not in (select user_id from task where creation_date between '2014-12-17' and '2014-12-23' group by user_id,creation_date order by creation_date);
select u.full_name,t.creation_date,SEC_TO_TIME(SUM(TIME_TO_SEC(case when creation_date = '2014-12-22' then hours else 0 end))) as Hours from users u left join task t on u.user_id = t.user_id group by u.user_id order by Hours;




delimiter //
drop procedure if exists dynamic_view2_all_users_task//
create procedure dynamic_view2_all_users_task(in sdate date,in edate date)
begin
declare finish int default 0;
declare cdate date;
declare str varchar(10000) default "select u.full_name, ";
declare curs cursor for select creation_date from task where creation_date between sdate and edate group by creation_date;
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, "SEC_TO_TIME(SUM(TIME_TO_SEC(case when creation_date = '",cdate,"' then hours else 0 end))) as `",cdate,"`,");
end loop;
close curs;

set str = substr(str,1,char_length(str)-1);
set @str = concat(str," from users u left join task t on u.user_id = t.user_id group by u.user_id order by u.full_name");

prepare stmt from @str;
execute stmt;
deallocate prepare stmt;
end;//
delimiter ;

call dynamic_view2_all_users_task('2014-12-01','2014-12-22');