Hi
We have a fire course and several others that staff complete annually but are having a bit of trouble running a report to find out who has lately passed.
When we run the report (based on the below SQL query) it will not show someone for example James Dean who completed the course in November 2018 and got a pass grade of 100%
However if we amend the date to the year before ie November 2017 his name will appear showing he completed it for that year. The course SCROM report gives the grade for 2018 but we need this report to work as we have several courses and need to match the data across to other systems.
Any help will be appreciated
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',
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:<%%
GROUP BY u.id, c.id, gg.id, gi.id,ctx.id, cc.id
ORDER BY u.lastname