SELECT users_kpis.user_id, users_kpis.id AS 'ID', users.name AS 'Employee Name', departments.title AS 'Department', CONCAT(users_kpis.year,'-',users_kpis.month) AS 'Year Month', users_kpis.total_employee_score AS 'Total Employee Score' , (SELECT FORMAT((SUM(users_kpis_questions.employee_actual)/SUM(users_kpis_questions.employee_target))*100,2) FROM `users_kpis_questions` WHERE users_kpi_id=users_kpis.id AND kpi_id=users_kpis.kpi_id AND user_id=users_kpis.user_id AND kpi_question_id!=203 AND deleted_at IS NULL) AS 'Total Employee Score Without target', (SELECT CASE WHEN FORMAT((SUM(users_kpis_questions.employee_actual)/SUM(users_kpis_questions.employee_target))*100,2) IS NULL THEN 0 ELSE FORMAT((SUM(users_kpis_questions.employee_actual)/SUM(users_kpis_questions.employee_target))*100,2) END FROM `users_kpis_questions` WHERE users_kpi_id=users_kpis.id AND kpi_id=users_kpis.kpi_id AND user_id=users_kpis.user_id AND kpi_question_id=203 AND deleted_at IS NULL) AS 'Total Employee Score Target Only', users_kpis.created_at AS 'Created At' FROM `users_kpis` INNER JOIN users ON users.id=users_kpis.user_id INNER JOIN departments ON departments.id=users.department_id WHERE user_id IN(SELECT id FROM users WHERE users.department_id=3 AND deleted_at IS NULL) AND year='2023' AND month='1' AND users_kpis.deleted_at IS NULL ================ SELECT users_kpis.id AS 'ID', users.name AS 'Employee Name', departments.title AS 'Department', CONCAT(users_kpis.year,'-',users_kpis.month) AS 'Year Month', users_kpis.total_employee_score AS 'Total Employee Score' , (SELECT FORMAT((SUM(users_kpis_questions.employee_actual)/SUM(users_kpis_questions.employee_target))*100,2) FROM `users_kpis_questions` WHERE users_kpi_id=users_kpis.id AND kpi_id=users_kpis.kpi_id AND user_id=users_kpis.user_id AND kpi_question_id!=203 AND deleted_at IS NULL) AS 'Total Employee Score Without target', (SELECT CASE WHEN FORMAT((SUM(users_kpis_questions.employee_actual)/SUM(users_kpis_questions.employee_target))*100,2) IS NULL THEN 0 ELSE FORMAT((SUM(users_kpis_questions.employee_actual)/SUM(users_kpis_questions.employee_target))*100,2) END FROM `users_kpis_questions` WHERE users_kpi_id=users_kpis.id AND kpi_id=users_kpis.kpi_id AND user_id=users_kpis.user_id AND kpi_question_id=203 AND deleted_at IS NULL) AS 'Total Employee Score Target Only', users_kpis.created_at AS 'Created At' FROM `users_kpis` INNER JOIN users ON users.id=users_kpis.user_id INNER JOIN departments ON departments.id=users.department_id WHERE user_id IN(SELECT id FROM users WHERE users.department_id=3 AND deleted_at IS NULL) AND year='2023' AND month='1' AND users_kpis.deleted_at IS NULL ====== SELECT CONCAT("UPDATE users_kpis_questions SET kpi_question_name='",REPLACE(kpis_questions.question,"'","\'"),"' WHERE id='",users_kpis_questions.id,"';") FROM `users_kpis_questions` INNER JOIN kpis_questions ON kpis_questions.id=users_kpis_questions.kpi_question_id UPDATE `users_kpis_questions` SET users_kpis_questions.kpi_question_name=(SELECT kpis_questions.question FROM kpis_questions WHERE kpis_questions.id=users_kpis_questions.kpi_question_id) WHERE users_kpis_questions.id=20 SELECT users_kpis.id AS 'ID', users.name AS 'Employee Name', departments.title AS 'Department', CONCAT(users_kpis.year,'-',users_kpis.month) AS 'Year Month', users_kpis.total_employee_score AS 'Total Employee Score' , (SELECT FORMAT((SUM(users_kpis_questions.employee_actual)),2) FROM `users_kpis_questions` WHERE users_kpi_id=users_kpis.id AND kpi_id=users_kpis.kpi_id AND user_id=users_kpis.user_id AND kpi_question_id!=203 AND deleted_at IS NULL) AS 'Total Employee Score Without target', (SELECT CASE WHEN FORMAT((SUM(users_kpis_questions.employee_actual)),2) IS NULL THEN 0 ELSE FORMAT((SUM(users_kpis_questions.employee_actual)),2) END FROM `users_kpis_questions` WHERE users_kpi_id=users_kpis.id AND kpi_id=users_kpis.kpi_id AND user_id=users_kpis.user_id AND kpi_question_id=203 AND deleted_at IS NULL) AS 'Total Employee Score Target Only', users_kpis.created_at AS 'Created At' FROM `users_kpis` INNER JOIN users ON users.id=users_kpis.user_id INNER JOIN departments ON departments.id=users.department_id WHERE user_id IN(SELECT id FROM users WHERE users.department_id=3 AND deleted_at IS NULL) AND year='2023' AND month='2' AND users_kpis.deleted_at IS NULL SELECT users_kpis.id AS 'ID', users.name AS 'Employee Name', departments.title AS 'Department', CONCAT(users_kpis.year,'-',users_kpis.month) AS 'Year Month', users_kpis.total_employee_score AS 'Total Employee Score' , (SELECT FORMAT((SUM(users_kpis_questions.employee_actual)),2) FROM `users_kpis_questions` WHERE users_kpi_id=users_kpis.id AND kpi_id=users_kpis.kpi_id AND user_id=users_kpis.user_id AND kpi_question_question!='target' AND deleted_at IS NULL) AS 'Total Employee Score Without target', (SELECT CASE WHEN FORMAT((SUM(users_kpis_questions.employee_actual)),2) IS NULL THEN 0 ELSE FORMAT((SUM(users_kpis_questions.employee_actual)),2) END FROM `users_kpis_questions` WHERE users_kpi_id=users_kpis.id AND kpi_id=users_kpis.kpi_id AND user_id=users_kpis.user_id AND kpi_question_question='target' AND deleted_at IS NULL) AS 'Total Employee Score Target Only', users_kpis.created_at AS 'Created At' FROM `users_kpis` INNER JOIN users ON users.id=users_kpis.user_id INNER JOIN departments ON departments.id=users.department_id WHERE user_id IN(SELECT id FROM users WHERE users.department_id=3 AND deleted_at IS NULL) AND year='2023' AND month='2' AND users_kpis.deleted_at IS NULL ----- SELECT users_kpis.id AS 'ID', users.name AS 'Employee Name', departments.title AS 'Department', CONCAT(users_kpis.year,'-',users_kpis.month) AS 'Year Month', users_kpis.total_employee_score AS 'Total Employee Score' , (SELECT FORMAT((SUM(users_kpis_questions.employee_actual)/SUM(users_kpis_questions.employee_target))*100,2) FROM `users_kpis_questions` WHERE users_kpi_id=users_kpis.id AND kpi_id=users_kpis.kpi_id AND user_id=users_kpis.user_id AND kpi_question_question!='target' AND deleted_at IS NULL) AS 'Total Employee Score Without target', (SELECT CASE WHEN FORMAT((SUM(users_kpis_questions.employee_actual)/SUM(users_kpis_questions.employee_target))*100,2) IS NULL THEN 0 ELSE FORMAT((SUM(users_kpis_questions.employee_actual)/SUM(users_kpis_questions.employee_target))*100,2) END FROM `users_kpis_questions` WHERE users_kpi_id=users_kpis.id AND kpi_id=users_kpis.kpi_id AND user_id=users_kpis.user_id AND kpi_question_question='target' AND deleted_at IS NULL) AS 'Total Employee Score Target Only', users_kpis.created_at AS 'Created At' FROM `users_kpis` INNER JOIN users ON users.id=users_kpis.user_id INNER JOIN departments ON departments.id=users.department_id WHERE user_id IN(SELECT id FROM users WHERE users.department_id=3 AND deleted_at IS NULL) AND year='2023' AND month='2' AND users_kpis.deleted_at IS NULL SELECT users_kpis.id AS 'ID', users.name AS 'Employee Name', departments.title AS 'Department', CONCAT(users_kpis.year,'-',users_kpis.month) AS 'Year Month', users_kpis.total_employee_score AS 'Total Employee Score' , (SELECT FORMAT((SUM(users_kpis_questions.employee_actual)),2) FROM `users_kpis_questions` WHERE users_kpi_id=users_kpis.id AND kpi_id=users_kpis.kpi_id AND user_id=users_kpis.user_id AND kpi_question_question!='target' AND deleted_at IS NULL) AS 'Total Employee Score Without target', (SELECT CASE WHEN FORMAT((SUM(users_kpis_questions.employee_actual)),2) IS NULL THEN 0 ELSE FORMAT((SUM(users_kpis_questions.employee_actual)),2) END FROM `users_kpis_questions` WHERE users_kpi_id=users_kpis.id AND kpi_id=users_kpis.kpi_id AND user_id=users_kpis.user_id AND kpi_question_question='target' AND deleted_at IS NULL) AS 'Total Employee Score Target Only', users_kpis.created_at AS 'Created At' FROM `users_kpis` INNER JOIN users ON users.id=users_kpis.user_id INNER JOIN departments ON departments.id=users.department_id WHERE user_id IN(SELECT id FROM users WHERE users.department_id=3 AND deleted_at IS NULL) AND year='2023' AND month='2' AND users_kpis.deleted_at IS NULL SELECT users_kpis.id AS 'ID', users.name AS 'Employee Name', departments.title AS 'Department', job_titles.title AS 'Job Titles', CONCAT(users_kpis.year,'-',users_kpis.month) AS 'Year Month', users_kpis.total_employee_score AS 'Total Employee Score' , (SELECT FORMAT((SUM(users_kpis_questions.employee_actual)),2) FROM `users_kpis_questions` WHERE users_kpi_id=users_kpis.id AND kpi_id=users_kpis.kpi_id AND user_id=users_kpis.user_id AND kpi_question_question!='target' AND deleted_at IS NULL) AS 'Total Employee Score Without target', (SELECT CASE WHEN FORMAT((SUM(users_kpis_questions.employee_actual)),2) IS NULL THEN 0 ELSE FORMAT((SUM(users_kpis_questions.employee_actual)),2) END FROM `users_kpis_questions` WHERE users_kpi_id=users_kpis.id AND kpi_id=users_kpis.kpi_id AND user_id=users_kpis.user_id AND kpi_question_question='target' AND deleted_at IS NULL) AS 'Total Employee Score Target Only', users_kpis.created_at AS 'Created At' FROM `users_kpis` INNER JOIN users ON users.id=users_kpis.user_id INNER JOIN departments ON departments.id=users.department_id INNER JOIN job_titles ON job_titles.id=users.job_title_id WHERE user_id IN(SELECT id FROM users WHERE users.department_id=3 AND deleted_at IS NULL) AND year='2023' AND month='4' AND users_kpis.deleted_at IS NULL SELECT users_kpis.id AS 'ID', users.name AS 'Employee Name', departments.title AS 'Department', job_titles.title AS 'Job Titles', CONCAT(users_kpis.year,'-',users_kpis.month) AS 'Year Month', users_kpis.total_employee_score AS 'Total Employee Score' , (SELECT FORMAT((SUM(users_kpis_questions.employee_actual)),2) FROM `users_kpis_questions` WHERE users_kpi_id=users_kpis.id AND kpi_id=users_kpis.kpi_id AND user_id=users_kpis.user_id AND kpi_question_question!='target' AND deleted_at IS NULL) AS 'Total Employee Score Without target', (SELECT CASE WHEN FORMAT((SUM(users_kpis_questions.employee_actual)),2) IS NULL THEN 0 ELSE FORMAT((SUM(users_kpis_questions.employee_actual)),2) END FROM `users_kpis_questions` WHERE users_kpi_id=users_kpis.id AND kpi_id=users_kpis.kpi_id AND user_id=users_kpis.user_id AND kpi_question_question='target' AND deleted_at IS NULL) AS 'Total Employee Score Target Only', users_kpis.created_at AS 'Created At' FROM `users_kpis` INNER JOIN users ON users.id=users_kpis.user_id INNER JOIN departments ON departments.id=users.department_id INNER JOIN job_titles ON job_titles.id=users.job_title_id WHERE user_id IN(SELECT id FROM users WHERE users.department_id=3 AND deleted_at IS NULL) AND year='2023' AND month='4' AND users_kpis.deleted_at IS NULL SELECT users_kpis.id AS 'ID', users.name AS 'Employee Name', departments.title AS 'Department', job_titles.title AS 'Job Titles', CONCAT(users_kpis.year,'-',users_kpis.month) AS 'Year Month', users_kpis.total_employee_score AS 'Total Employee Score' , (SELECT FORMAT((SUM(users_kpis_questions.employee_actual)),2) FROM `users_kpis_questions` WHERE users_kpi_id=users_kpis.id AND kpi_id=users_kpis.kpi_id AND user_id=users_kpis.user_id AND kpi_question_question!='target' AND deleted_at IS NULL) AS 'Total Employee Score Without target', (SELECT CASE WHEN FORMAT((SUM(users_kpis_questions.employee_actual)),2) IS NULL THEN 0 ELSE FORMAT((SUM(users_kpis_questions.employee_actual)),2) END FROM `users_kpis_questions` WHERE users_kpi_id=users_kpis.id AND kpi_id=users_kpis.kpi_id AND user_id=users_kpis.user_id AND kpi_question_question='target' AND deleted_at IS NULL) AS 'Total Employee Score Target Only', users_kpis.created_at AS 'Created At' FROM `users_kpis` INNER JOIN users ON users.id=users_kpis.user_id INNER JOIN departments ON departments.id=users.department_id INNER JOIN job_titles ON job_titles.id=users.job_title_id WHERE user_id IN(SELECT id FROM users WHERE users.department_id=3 AND deleted_at IS NULL) AND year='2023' AND month='5' AND users_kpis.deleted_at IS NULL