Hi All,
I do realise there was another discussion on this from 2017 but it is locked and unable to add to the post.
Apologies in advance I am new to this community
As per the above, I am wanting to write a configurable report that shows only users that have not attempted the quiz. Below is the SQL I have put together to try and get what I am after. If anyone could offer me some assistance would be greatly appreciated. Code below so far
-----
Select
c.id AS "Course ID",
c.fullname AS "Course Name",
cm.id as "Quiz ID",
q.name AS "Quiz Name",
u.idnumber AS "User ID",
CONCAT(u.firstname, ' ', u.lastname) AS "User's name",
ss.attempt AS "Attempt"
FROM
prefix_quiz q
INNER JOIN prefix_course c ON q.course = c.id
INNER JOIN prefix_course_categories cc ON c.category = cc.id
INNER JOIN prefix_course_modules cm ON cm.instance = q.id and cm.module = 16
INNER JOIN prefix_enrol e ON e.courseid = c.id
INNER JOIN prefix_user_enrolments ue on ue.enrolid = e.id
INNER JOIN prefix_user u ON u.id = ue.userid
FULL OUTER JOIN prefix_scorm_aicc_session ss ON ss.userid = u.id
LEFT JOIN prefix_scorm s ON s.course = c.id
where
c.fullname = 'FIT1051 2021 Sem 2 (Australia)'
GROUP BY
c.id,
c.fullname,
cm.id,
q.name,
u.id,
u.firstname,
u.lastname,
ss.attempt