Aug
07
MySQL dynamic pivot table
I’m using Redmine to manage projects and bug reports, I was needing a daily report with spent time by user on each project, nothing complicated, but I wanted a cross-reference result like Excel dynamic tables. As we know, MySQL doesn’t support dynamic cross-reference queries but I make an approach:
1. Write the main query with rows: project identifier, task subject and total hours in a date range.
SELECT p.identifier, e.issue_id, i.subject, round(SUM(e.hours),2) AS total_hours FROM redmine_nsp.time_entries e INNER JOIN redmine_nsp.issues i ON e.issue_id = i.id INNER JOIN redmine_nsp.projects p ON e.project_id = p.id WHERE e.spent_on BETWEEN '2014-07-01' AND '2014-07-07' GROUP BY p.identifier,e.issue_id;
+------------+----------+----------------------------+-------------+ | identifier | issue_id | subject | total_hours | +------------+----------+----------------------------+-------------+ | bg02 | 3223 | Gestion de proyecto | 0.25 | | bg04 | 3256 | 1.1 Preparación del entor | 0.63 | | emision | 3251 | Desarrollar la aplicación | 3.97 | | nsp00 | 3236 | Preparar propuesta para G | 2.02 | | nsp02 | 3234 | Subida al servidor de pro | 0.52 | | nsp02 | 3240 | Agregar funcionalidad de | 0.55 | | nsp02 | 3241 | Revertir el Documento | 2.80 | | nsp02 | 3242 | Agregar Filtros en las vi | 0.72 | | nsp02 | 3243 | Reportes de Cargo de entr | 2.35 | | nsp02 | 3254 | Control de sesiones | 3.23 | | nsp05 | 3252 | Mantenimiento del servido | 0.18 | | nsp05 | 3253 | Mantenimiento a redmine | 0.53 | | nsp06 | 3203 | Elaborar el visualzador d | 0.23 | | nsp06 | 3228 | Evitar que se tome mas de | 0.25 | | nsp06 | 3255 | Actualizar el porcentaje | 1.50 | | nsp08 | 3239 | Asistencia remota | 1.38 | +------------+----------+----------------------------+-------------+
2. Then list the active users between dates:
SELECT e.user_id, u.login FROM redmine_nsp.time_entries e INNER JOIN redmine_nsp.users u ON e.user_id = u.id WHERE e.spent_on BETWEEN '2014-07-01' AND '2014-07-07' GROUP BY e.user_id;
+---------+----------+ | user_id | login | +---------+----------+ | 1 | user1 | | 4 | user2 | | 5 | user3 | | 6 | user4 | +---------+----------+
3. Now, create a stored procedure to create a dynamic SQL command with the second query as column names and values with each Redmine issue. The second query is used as cursor.
DELIMITER $$ CREATE PROCEDURE `get_range_summary`(dDate1 DATE,dDate2 DATE) BEGIN DECLARE done INT DEFAULT 0; DECLARE p_user_id INT; DECLARE p_sql text; DECLARE p_login VARCHAR(255); DECLARE c_users cursor FOR SELECT e.user_id, u.login FROM redmine_nsp.time_entries e INNER JOIN redmine_nsp.users u ON e.user_id = u.id WHERE e.spent_on BETWEEN dDate1 AND dDate2 GROUP BY e.user_id; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; SET p_sql = 'select s.codigo,s.issue_id,s.subject '; OPEN c_users; read_loop: LOOP FETCH c_users INTO p_user_id, p_login; IF done THEN LEAVE read_loop; END IF; SET p_sql = concat(p_sql, ', (select round(sum(t.hours),2) as total from redmine_nsp.time_entries t where t.spent_on between \'',dDate1,'\' and \'',dDate2,'\' and t.issue_id = s.issue_id and t.user_id = ',p_user_id,') as `',p_login,'` '); END LOOP; SET @SQL = concat(p_sql,' ,s.total_hours from (select p.identifier, e.issue_id, i.subject, round(sum(e.hours),2) as total_hours from redmine_nsp.time_entries e inner join redmine_nsp.issues i on e.issue_id = i.id inner join redmine_nsp.projects p on e.project_id = p.id where e.spent_on between \'',dDate1,'\' and \'',dDate2,'\' group by p.identifier,e.issue_id) as s'); close c_users; PREPARE stmt1 FROM @SQL; EXECUTE stmt1; DEALLOCATE PREPARE stmt1; END
The result of call get_range_summary(‘2014-07-01′,’2014-07-07’):
+---------+----------+----------------------------+-------+--------+---------+----------+-------------+ | identif | issue_id | subject | user1 | user2 | user3 | user4 | total_hours | +---------+----------+----------------------------+-------+--------+---------+----------+-------------+ | bg02 | 23 | Gestion de proyecto | NULL | 0.25 | NULL | NULL | 0.25 | | bg04 | 56 | 1.1 Preparación del entor | NULL | 0.63 | NULL | NULL | 0.63 | | emision | 51 | Desarrollar la aplicación | NULL | NULL | 3.97 | NULL | 3.97 | | nsp00 | 36 | Preparar propuesta para G | NULL | 2.02 | NULL | NULL | 2.02 | | nsp02 | 34 | Subida al servidor de pro | NULL | 0.52 | NULL | NULL | 0.52 | | nsp02 | 40 | Agregar funcionalidad de | NULL | NULL | 0.55 | NULL | 0.55 | | nsp02 | 41 | Revertir el Documento | 1.40 | NULL | 1.40 | NULL | 2.80 | | nsp02 | 42 | Agregar Filtros en las vi | NULL | NULL | 0.72 | NULL | 0.72 | | nsp02 | 43 | Reportes de Cargo de entr | NULL | NULL | NULL | 2.35 | 2.35 | | nsp02 | 54 | Control de sesiones | NULL | NULL | NULL | 3.23 | 3.23 | | nsp05 | 52 | Mantenimiento del servidor | NULL | 0.18 | NULL | NULL | 0.18 | | nsp05 | 53 | Mantenimiento a redmine | NULL | 0.53 | NULL | NULL | 0.53 | | nsp06 | 3 | Elaborar el visualzador de | NULL | 0.23 | NULL | NULL | 0.23 | | nsp06 | 28 | Evitar que se tome mas de | NULL | 0.25 | NULL | NULL | 0.25 | | nsp06 | 55 | Actualizar el porcentaje d | NULL | 1.50 | NULL | NULL | 1.50 | | nsp08 | 39 | Asistencia remota | NULL | 1.38 | NULL | NULL | 1.38 | +---------+----------+----------------------------+-------+--------+---------+----------+-------------+
That’s it. I put the result easily in a php based report and added it to cron task.