Hello all,
So I am working with ad hoc reports plugin of moodle. I have a SQL query which shows all course completions in past 24 hours. However, I want to display all course completions in past 24 hours for which the final grade =1.
Please let me know how to achieve this. Currently, I have the following SQL code:
DATE_FORMAT(FROM_UNIXTIME(p.timecompleted),'%Y-%m-%d') AS completed
FROM prefix_course_completions AS p
JOIN prefix_course AS c ON p.course = c.id
JOIN prefix_user AS u ON p.userid = u.id
JOIN prefix_grade_grades AS gg ON gg.userid = u.id
JOIN prefix_grade_items AS gi ON gi.id = gg.itemid
WHERE c.enablecompletion = 1
AND (p.timecompleted>= UNIX_TIMESTAMP(DATE_SUB(NOW(),INTERVAL 24 HOUR)))
ORDER BY u.username