Archive: SQL for moodle

Budget 12$ per month

Posted: 5 years ago

Closed

Description: We need someone to re-write us a moodle query report SQl. Our current one is below which we filter by date,

However say someone has done the fire course they will appear when you filter the results for December 2017. However when they retook the course again in November 2018 they don't not appear in the report. They do howver appear in the SCORM report for that course.

Current
SELECT
u.username AS Username,u.firstname AS 'First' , u.lastname AS 'Last', u.email AS 'User Email',
dept.data Department,
c.fullname AS 'Course',
gi.itemname AS 'Activity',
ROUND(gg.finalgrade,0) as Grade,
DATE_FORMAT( FROM_UNIXTIME( gg.timemodified ) , '%d/%m/%Y' ) AS date
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_role AS r ON r.id = ra.roleid AND r.shortname = 'student'
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
LEFT JOIN prefix_user_info_data dept on dept.userid = u.id and dept.fieldid = 3
LEFT JOIN prefix_user_info_data spec on spec.userid = u.id and spec.fieldid = 1
WHERE gi.courseid = c.id AND gi.itemtype = 'mod'
AND gg.finalgrade>= '65'
%%FILTER_STARTTIME:gg.timemodified:>%% %%FILTER_ENDTIME:gg.timemodified: