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

















