I'm trying to add a site level report that shows whether a student has completed all enrolled courses, and displays 6 custom profile fields as columns. My SQL skills are pretty basic at the moment.
SELECT CONCAT (u.firstname, u.lastname) Name, d.data Custom_Field,
IF (ROUND(g.finalgrade / g.rawgrademax * 100, 2) > 79, 'Yes', 'No') Complete
FROM prefix_user u
JOIN prefix_user_info_data d ON d.userid = u.id
JOIN prefix_user_info_field f ON d.fieldid = f.id
JOIN prefix_grade_grades g ON g.userid = u.id
WHERE f.shortname = "Custom_Field"
ORDER BY `Name` ASC
The above works for 1 custom field, but I'm not sure how to go about adding others, since they'd also be pulling rows of d.data and would have to have different associated WHERE statements for each column. Any insight would be very much appreciated. Thank you!
I may be barking up the wrong tree on the enrolled course completion column as well - I think my current would only display on the course level for the current course. Help on that would also be appreciated.