Hi, I'm new to SQL so am struggling with producing a custom report.
I need to produce a report that not only tells the client which activities have been completed, but the activities which have not been completed. This is where I'm struggling. There doesn't appear to be a database entry for each user created for an activity unless they interact with the activity. Is there a way to set all activities (cm.completion in the prefix_course_modules_completion cmc database) to either NULL or 0 as default?
Below is the Query SQL that I'm using for guidance.
Thanks so kindly in advance, I'm quickly losing all my hair!
SELECT
u.firstname AS 'Firstname',
u.lastname AS 'Lastname',
u.email AS 'Email',
CONCAT(u.firstname, ' ', u.lastname) AS `Name`,
c.shortname AS 'Course',
m.name AS Activitytype,
CASE
WHEN cm.module = 1 THEN (SELECT a1.name FROM prefix_assign a1 WHERE a1.id = cm.instance)
WHEN cm.module = 2 THEN (SELECT a2.name FROM prefix_assignment a2 WHERE a2.id = cm.instance)
WHEN cm.module = 3 THEN (SELECT a3.name FROM prefix_book a3 WHERE a3.id = cm.instance)
WHEN cm.module = 4 THEN (SELECT a4.name FROM prefix_chat a4 WHERE a4.id = cm.instance)
WHEN cm.module = 5 THEN (SELECT a5.name FROM prefix_choice a5 WHERE a5.id = cm.instance)
WHEN cm.module = 6 THEN (SELECT a6.name FROM prefix_data a6 WHERE a6.id = cm.instance)
WHEN cm.module = 7 THEN (SELECT a7.name FROM prefix_feedback a7 WHERE a7.id = cm.instance)
WHEN cm.module = 8 THEN (SELECT a8.name FROM prefix_folder a8 WHERE a8.id = cm.instance)
WHEN cm.module = 9 THEN (SELECT a9.name FROM prefix_forum a9 WHERE a9.id = cm.instance)
WHEN cm.module = 10 THEN (SELECT a10.name FROM prefix_glossary a10 WHERE a10.id = cm.instance)
WHEN cm.module = 11 THEN (SELECT a11.name FROM prefix_imscp a11 WHERE a11.id = cm.instance)
WHEN cm.module = 12 THEN (SELECT a12.name FROM prefix_label a12 WHERE a12.id = cm.instance)
WHEN cm.module = 13 THEN (SELECT a13.name FROM prefix_lesson a13 WHERE a13.id = cm.instance)
WHEN cm.module = 14 THEN (SELECT a14.name FROM prefix_lti a14 WHERE a14.id = cm.instance)
WHEN cm.module = 15 THEN (SELECT a15.name FROM prefix_page a15 WHERE a15.id = cm.instance)
WHEN cm.module = 16 THEN (SELECT a16.name FROM prefix_quiz a16 WHERE a16.id = cm.instance)
WHEN cm.module = 17 THEN (SELECT a17.name FROM prefix_resource a17 WHERE a17.id = cm.instance)
WHEN cm.module = 18 THEN (SELECT a18.name FROM prefix_scorm a18 WHERE a18.id = cm.instance)
WHEN cm.module = 19 THEN (SELECT a19.name FROM prefix_survey a19 WHERE a19.id = cm.instance)
WHEN cm.module = 20 THEN (SELECT a20.name FROM prefix_url a20 WHERE a20.id = cm.instance)
WHEN cm.module = 21 THEN (SELECT a21.name FROM prefix_wiki a21 WHERE a21.id = cm.instance)
WHEN cm.module = 22 THEN (SELECT a22.name FROM prefix_workshop a22 WHERE a22.id = cm.instance)
END AS Actvityname,
# cm.section AS Coursesection,
CASE
WHEN cm.completion = NULL THEN 'Not completed'
WHEN cm.completion = 0 THEN '0 None'
WHEN cm.completion = 1 THEN '1 Self'
WHEN cm.completion = 2 THEN '2 Auto'
END AS Activtycompletiontype,
CASE
WHEN cmc.completionstate = NULL THEN 'Not completed'
WHEN cmc.completionstate = 0 THEN 'Not completed'
WHEN cmc.completionstate = 1 THEN 'Completed'
WHEN cmc.completionstate = 2 THEN 'Completed with Pass'
WHEN cmc.completionstate = 3 THEN 'Completed with Fail'
ELSE 'Unknown'
END AS 'Progress',
CASE
WHEN cmc.viewed = 0 THEN 'NO'
WHEN cmc.viewed = 1 THEN 'YES'
END AS 'Agreed',
DATE_FORMAT(FROM_UNIXTIME(cmc.timemodified), '%Y-%m-%d %H:%i') AS 'When',
(SELECT data FROM prefix_user_info_data WHERE prefix_user_info_data.userid=u.id AND prefix_user_info_data.fieldid = 5) AS 'Employee Number',
(SELECT data FROM prefix_user_info_data WHERE prefix_user_info_data.userid=u.id AND prefix_user_info_data.fieldid = 3) AS 'Job Role',
(SELECT data FROM prefix_user_info_data WHERE prefix_user_info_data.userid=u.id AND prefix_user_info_data.fieldid = 1) AS 'Business Area',
(SELECT data FROM prefix_user_info_data WHERE prefix_user_info_data.userid=u.id AND prefix_user_info_data.fieldid = 6) AS Team
FROM prefix_course_modules_completion cmc
JOIN prefix_user u ON cmc.userid = u.id
JOIN prefix_course_modules cm ON cmc.coursemoduleid = cm.id
JOIN prefix_course c ON cm.course = c.id
JOIN prefix_modules m ON cm.module = m.id
# skip the predefined admin AND guest USER
WHERE u.id > 2 AND c.id=51
ORDER BY u.username