Hello, I'm working on a report where I am providing all of the grades for a quiz that specific cohorts have taken, however now I've been asked to add the last login for users in this cohort (not necessarily related to this grade event).
I need a little assistance with the code to pull last log in information. A lot of the information I've found is for older versions of moodle and don't work in this case.
Code in Question is below:
SELECT
u.firstname AS 'Name',
u.lastname AS 'Surname',
c.fullname AS 'Course',
ch.name AS 'Cohort',
CASE WHEN gi.itemtype = 'Course'
THEN c.fullname + ' Course Total'
ELSE gi.itemname
END AS 'Item Name',
ROUND(gg.finalgrade,2) AS Score,
ROUND(gg.rawgrademax,2) AS MAX, ROUND(gg.finalgrade / gg.rawgrademax * 100 ,2) AS Percentage
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_cohort_members as cm ON u.id = cm.userid
JOIN prefix_cohort as ch ON cm.cohortid = ch.id
WHERE ch.name = '****' AND gi.aggregationcoef2 != 0 and c.fullname = '**** Certification v. 1.1'AND gi.itemname = '**** Solutions Certification Exam v. 1.1'
ORDER BY `Name` ASC
If this is the wrong forum, or perhaps would be a better fit to try and generate in intelliboard, please let me know.
Thank you.