https://docs.moodle.org/28/en/ad-hoc_contributed_reports#Learner_report_by_Learner_with_grades
- Subject Completion progress status. such as: Not Yet Started, In Progress, Completed or Credit Transfer
- to include incomplete subjects as well.
SELECT u.firstname AS'Name', u.lastname AS'Surname', c.fullname AS'Course', cc.name AS'Category',
CASEWHEN gi.itemtype ='Course'
THEN c.fullname +' Course Total'
ELSE gi.itemname
ENDAS'Item Name', ROUND(gg.finalgrade,2)AS Score,ROUND(gg.rawgrademax,2)ASMAX, ROUND(gg.finalgrade / gg.rawgrademax *100,2)AS Percentage,
IF(ROUND(gg.finalgrade / gg.rawgrademax *100,2)>79,'Yes','No')AS Pass
FROM prefix_course AS c
JOIN prefix_context AS ctx ON c.id = ctx.instanceid
JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id
JOIN prefix_user AS u ON u.id = ra.userid
JOIN prefix_grade_grades AS gg ON gg.userid = u.id
JOIN prefix_grade_items AS gi ON gi.id = gg.itemid
JOIN prefix_course_categories AS cc ON cc.id = c.category
WHERE gi.courseid = c.id AND gi.itemname !='Attendance'
ORDERBY`Name`ASC