Hi All
I would like to share a query (postgres) that myself and my it server admin put together, we are learning SQL queries together.
It returns all the users who have completed all the quizzes (with some exceptions) based on the users most recent attempt.
What i would like someone to do if possible (as we have tried and tried but with no success) is to return all the users who have not done the quizzes in the same query.
Thank You
Gary
SELECT u.idnumber AS "Employee Number",
u.firstname AS "First Name",
u.lastname AS "Last Name",
u.description AS "Role",
u.phone1 AS "Command",
u.institution AS "Station",
u.department AS "Department",
c.fullname AS "Course Name",
upper(c.idnumber::text) AS "Course ID number",
q.name AS "Assessment Name",
ROUND (g.grade,1) AS "Marks Achieved",
ROUND (q.grade,0) AS "Total Marks",
ROUND( (g.grade/q.grade)*100, 1) AS "Percentage",
a.state AS "Assessment Progress",
to_char(to_timestamp(a.timestart::double precision)::timestamp without time zone::date::timestamp with time zone, 'DD-MM-YYYY'::text) AS "Assessment Started",
to_char(to_timestamp(a.timefinish::double precision)::timestamp without time zone::date::timestamp with time zone, 'DD-MM-YYYY'::text) AS "Assessment Finished"
FROM ( SELECT x.userid,
x.quiz,
max(x.attempt) AS finattempt
FROM ( SELECT mdl_quiz_attempts.userid,
mdl_quiz_attempts.quiz,
mdl_quiz_attempts.attempt
FROM mdl_quiz_attempts
WHERE mdl_quiz_attempts.state::text = 'finished'::text) x
GROUP BY x.userid, x.quiz) k
JOIN mdl_quiz_attempts a ON k.userid = a.userid AND k.quiz = a.quiz AND k.finattempt = a.attempt
JOIN mdl_quiz q ON k.quiz = q.id
JOIN mdl_course c ON q.course = c.id
JOIN mdl_quiz_grades g ON k.userid = g.userid AND k.quiz = g.quiz
JOIN mdl_user u ON k.userid = u.id
WHERE c.idnumber::text <> ''::text AND u.suspended = 0 AND u.deleted = 0 AND q.id <> 95::bigint AND q.id <> 355::bigint AND q.id <> 360::bigint AND q.id <> 359::bigint AND u.department = ' A Place'
ORDER BY u.institution,q.name,u.description
;