I have following task table:
id | client_id | user_id | designation_id | total_hours
-----------------------------------------------------------------
1 | 14 | 134 | 1 | 4
2 | 14 | 135 | 2 | 1
3 | 15 | 136 | 3 | 3
4 | 16 | 137 | 4 | 4.5
And designations table:
id | title
------------------------
1 | Cloud Analyst
2 | Data Analyst
3 | Data QA Analyst
4 | Project Manager
.
.
And I have a stored procedure that pivots each records in designations table into columns (dynamic):
DROP PROCEDURE IF EXISTS clientWiseHoursReport;
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `clientWiseHoursReport`(IN `start_date` DATETIME, IN `end_date` DATETIME)
BEGIN
SET GLOBAL group_concat_max_len=4294967295;
SET @SQL = NULL;
SET @start_date = DATE(start_date);
SET @end_date = DATE(end_date);
SELECT
COALESCE(GROUP_CONCAT(DISTINCT
CONCAT(
'(CASE WHEN tsk.designation_id = "',id,'" THEN total_hours ELSE NULL END) AS `',title,'`'
) ORDER BY title
), '0 as `NoMatchingRows`') INTO @SQL
FROM `designations`
WHERE is_pivotable = 1;
SET @SQL
= CONCAT
(
'
SELECT cnt.name as Client, concat(usr.firstname," ",usr.lastname) as Name, ttype.name as "Project Type", task_details as "Task Detail", ', @SQL, '
FROM
task as tsk
LEFT JOIN client cnt
ON tsk.client_id = cnt.id
LEFT JOIN task_type ttype
ON tsk.task_type_id = ttype.id
LEFT JOIN users usr
ON tsk.user_id = usr.id
WHERE ((DATE(task_completion) BETWEEN "',@start_date,'" AND "',@end_date,'") OR
(DATE(assigned_date) BETWEEN "',@start_date,'" AND "',@end_date,'") OR
(DATE(task_deadline) BETWEEN "',@start_date,'" AND "',@end_date,'"))
'
);
PREPARE stmt FROM @SQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;
This gives the result as:
client | user | Cloud Analyst | Data Analyst | Data QA Analyst | Project Manager
-------------------------------------------------------------------------------------------
14 | 134 | 4 | | |
14 | 135 | | 1 | |
15 | 136 | | | 3 |
16 | 137 | | | | 4.5
What I am trying to get is SUM of each dynamic designation columns grouped by client_id.
LIKE :
client | user | Cloud Analyst | Data Analyst | Data QA Analyst | Project Manager
-------------------------------------------------------------------------------------------
14 | 134 | 4 | | |
14 | 135 | 0.5 | | |
14 | null | 4.5 | | |
If, say, columns were known, it would be:
.
.
UNION ALL
SELECT client, NULL, NULL.., SUM('Cloud Analyst') as c1, SUM('Data Analyst') as d1....
something like that..
But I got stuck with the dynamic case.
I found a similar solution but in sql-server and I couldn't crack it.
Hoping someone could help me with this.
