Hi,
I'm using a custom report to get some summary information about various scorm courses. I've got most of the data I need, but there's one column that I can't work out how to calculate. We need the percentage of the course that each user has completed. I must admit that I'm far from an expert in this area, I've basically cobbled together the SQL from the list of useful queries in the ad-hoc custom reports documentation. Here's the SQL I'm using:
SELECT u.firstname, u.lastname, c.shortname,
DATE_FORMAT(DATE(FROM_UNIXTIME(ue.timestart)), '%d/%m/%Y') start_date,
SEC_TO_TIME(SUM( TIME_TO_SEC( t.value ) ) ) total_time,
ROUND(gg.finalgrade / gg.rawgrademax * 100 ,2) AS final_grade
FROM prefix_course c
JOIN prefix_context ctx ON c.id = ctx.instanceid
JOIN prefix_role_assignments ra ON ra.contextid = ctx.id
JOIN prefix_user u ON u.id = ra.userid
JOIN prefix_grade_grades gg ON gg.userid = u.id
JOIN prefix_grade_items gi ON gi.id = gg.itemid
JOIN prefix_course_categories cc ON cc.id = c.category
JOIN prefix_scorm_scoes_track t ON t.userid = u.id
JOIN prefix_scorm s ON s.course = c.id
JOIN prefix_user_enrolments ue ON ue.userid = u.id
WHERE gi.courseid = c.id
AND gi.itemtype = 'course'
AND u.deleted = 0
AND t.scormid = s.id
AND t.element = 'cmi.core.total_time'
GROUP BY u.firstname, u.lastname
My question is, is it possible to extract / calculate the percentage of the course that's been completed for each user? I've been looking around the query examples and the database schema but I'm a bit lost... if anyone can help me and point me in the right direction it'll be greatly appreciated!
Many thanks
Dave