Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT s.sur_id, s.sur_created_at, emp.emp_sys_id, emp.emp_vhur AS emp_wd, emp.emp_full_name AS emp_name, p.pos_name, sup.emp_full_name AS sup_name, cre.emp_full_name AS creator_name, u.uni_name, cre.emp_full_name AS created_by, MAX(IF((q.que_id = 1 AND a.ans_type_id = 2), a.ans_content, NULL)) AS 'ans_que_1', MAX(IF((q.que_id = 2 AND a.ans_type_id = 2), a.ans_content, NULL)) AS 'ans_que_2', MAX(IF((q.que_id = 3 AND a.ans_type_id = 2), a.ans_content, NULL)) AS 'ans_que_3', MAX(IF((q.que_id = 4 AND a.ans_type_id = 2), a.ans_content, NULL)) AS 'ans_que_4', MAX(IF((q.que_id = 5 AND a.ans_type_id = 2), a.ans_content, NULL)) AS 'ans_que_5', MAX(IF((q.que_id = 6 AND a.ans_type_id = 2), a.ans_content, NULL)) AS 'ans_que_6', GROUP_CONCAT(IF((q.que_id = 7 AND a.ans_type_id = 4), a.ans_content, IF((q.que_id = 7 AND a.ans_type_id = 1 AND a.ans_content = 1), 'YES', IF((q.que_id = 7 AND a.ans_type_id = 1 AND a.ans_content = 2),'NO', NULL)))) AS 'opc_que_7', MAX(IF((q.que_id = 7 AND a.ans_type_id = 2), a.ans_content, NULL)) AS 'ans_que_7', GROUP_CONCAT(IF((q.que_id = 8 AND a.ans_type_id = 4), a.ans_content, IF((q.que_id = 8 AND a.ans_type_id = 1 AND a.ans_content = 1), 'YES', IF((q.que_id = 8 AND a.ans_type_id = 1 AND a.ans_content = 2),'NO', NULL)))) AS 'opc_que_8', MAX(IF((q.que_id = 8 AND a.ans_type_id = 2), a.ans_content, NULL)) AS 'ans_que_8', GROUP_CONCAT(IF((q.que_id = 9 AND a.ans_type_id = 4), a.ans_content, IF((q.que_id = 9 AND a.ans_type_id = 1 AND a.ans_content = 1), 'YES', IF((q.que_id = 9 AND a.ans_type_id = 1 AND a.ans_content = 2),'NO', NULL)))) AS 'opc_que_9', MAX(IF((q.que_id = 9 AND a.ans_type_id = 2), a.ans_content, NULL)) AS 'ans_que_9', GROUP_CONCAT(IF((q.que_id = 10 AND a.ans_type_id = 4), a.ans_content, IF((q.que_id = 10 AND a.ans_type_id = 1 AND a.ans_content = 1), 'YES', IF((q.que_id = 10 AND a.ans_type_id = 1 AND a.ans_content = 2),'NO', NULL)))) AS 'opc_que_10', MAX(IF((q.que_id = 10 AND a.ans_type_id = 2), a.ans_content, NULL)) AS 'ans_que_10', GROUP_CONCAT(IF((q.que_id = 11 AND a.ans_type_id = 4), a.ans_content, IF((q.que_id = 11 AND a.ans_type_id = 1 AND a.ans_content = 1), 'YES', IF((q.que_id = 11 AND a.ans_type_id = 1 AND a.ans_content = 2),'NO', NULL)))) AS 'opc_que_11', MAX(IF((q.que_id = 11 AND a.ans_type_id = 2), a.ans_content, NULL)) AS 'ans_que_11', GROUP_CONCAT(IF((q.que_id = 12 AND a.ans_type_id = 4), a.ans_content, IF((q.que_id = 12 AND a.ans_type_id = 1 AND a.ans_content = 1), 'YES', IF((q.que_id = 12 AND a.ans_type_id = 1 AND a.ans_content = 2),'NO', NULL)))) AS 'opc_que_12', MAX(IF((q.que_id = 12 AND a.ans_type_id = 2), a.ans_content, NULL)) AS 'ans_que_12', MAX(IF((q.que_id = 13 AND a.ans_type_id = 4), a.ans_content, NULL)) AS 'ans_mul_13'
- FROM pfu_h2g_survey s
- INNER JOIN pfu_employees emp ON emp.emp_sys_id = s.sur_employee_id
- INNER JOIN pfu_employees sup ON sup.emp_sys_id = s.sur_supervisor_id
- INNER JOIN pfu_employees cre ON cre.emp_sys_id = s.sur_created_by
- INNER JOIN pfu_unit u ON u.uni_sys_id = emp.emp_unit_id
- INNER JOIN pfu_position p ON p.pos_sys_id = emp.emp_pos_id
- INNER JOIN pfu_h2g_answer_per_question ap ON ap.apq_survey_id = s.sur_id
- INNER JOIN pfu_h2g_answers a ON a.ans_id = ap.apq_answer_id
- INNER JOIN pfu_h2g_questions q ON q.que_id = ap.apq_question_id
- WHERE s.sur_active = 1 AND s.sur_created_at >= '2018-11-01 00:00:00' AND s.sur_created_at <= '2018-11-30 23:59:59' AND (u.uni_sys_id IN('307') OR u.uni_parent_id IN ('307')) AND s.sur_employee_id IN() AND q.que_id IN (1,2,3,4,5,6,7,8,9,10,11,12,13)
- GROUP BY s.sur_id, s.sur_created_at, emp.emp_sys_id, emp_wd, emp_name, p.pos_name,sup_name, creator_name, u.uni_name, created_by
- ORDER BY s.sur_created_at DESC;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
