Dear Experts,
For a majority of my courses I have been using enrollment method, Cohort Sync. This work well except when I remove a student from a cohort, as cohort's do not record a removal date/timestamp.
My Failure query, reports enrolled students without a pass grade. After years of running as expected, two students appeared which were remove from the respective cohorts.
Is there something I am missing to only report on students with active enrollments?
SELECT DISTINCT
CONCAT(prefix_user.firstname, ' ', prefix_user.lastname) AS Employee,
email,
prefix_grade_items.gradepass AS pointstopass,
ROUND(prefix_grade_grades.finalgrade, 0) AS pointsobtained,
ROUND(prefix_grade_grades.finalgrade / prefix_grade_items.grademax * 100, 0) AS finalgradepercent,
(CASE WHEN ROUND(prefix_grade_grades.finalgrade, 0) >= prefix_grade_items.gradepass THEN 'Passed' ELSE 'Failed' END) AS Status,
prefix_course.fullname as course_name,
CONCAT('%%WWWROOT%%/course/view.php',CHAR(63),'id=',prefix_course.id) as course_name_link_url
FROM prefix_grade_grades
INNER JOIN prefix_grade_items ON prefix_grade_grades.itemid = prefix_grade_items.id
INNER JOIN prefix_course ON prefix_grade_items.courseid = prefix_course.id
INNER JOIN prefix_course_categories ON prefix_course.category = prefix_course_categories.id
INNER JOIN prefix_user ON prefix_grade_grades.userid = prefix_user.id
Right JOIN prefix_enrol en ON en.courseid = prefix_course.id
Right JOIN prefix_user_enrolments as ue on ue.userid = prefix_user.id
Right JOIN prefix_course_completion_crit_compl as t on t.course = prefix_course.id
WHERE (prefix_grade_items.itemname IS NOT NULL)
AND (prefix_grade_items.itemmodule = 'quiz' OR prefix_grade_items.itemmodule IS NULL)
AND (prefix_grade_grades.timemodified IS NOT NULL)
AND prefix_user.deleted = 0 AND prefix_user.suspended = 0
AND ROUND(prefix_grade_grades.finalgrade, 0) < prefix_grade_items.gradepass
AND t.unenroled IS NULL
AND ue.status = 0
Order by prefix_course.fullname
Kind Regards