I need a report that shows, for a selected user and course, in a given period, a list of all grade items that have their grades modified, and lists current grade and previous grade. I am unable to get the previous grade. Here is my code so far, any help will be vastly appreciated. Thanks
SELECT
CASE WHEN gc.fullname <=> NULL
THEN ' '
ELSE gc.fullname
END AS 'Category' ,
CASE WHEN gi.itemtype = 'Course'
THEN c.fullname + ' Course Total'
ELSE gi.itemname
END AS 'Item Name',
CASE WHEN ROUND(gg.finalgrade,0) <=> 1
THEN SUBSTRING_INDEX(s.scale,',',1)
WHEN ROUND(gg.finalgrade,0) <=> 2
THEN SUBSTRING_INDEX(SUBSTRING_INDEX(s.scale,',',2),',',-1)
WHEN ROUND(gg.finalgrade,0) <=> 3
THEN SUBSTRING_INDEX(s.scale,',',-1)
WHEN gg.finalgrade <=> NULL
THEN ' '
ELSE s.scale
END AS 'Assesment', gg.feedback AS 'Feedback Comments'
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_categories AS gc ON gc.courseid = c.id
JOIN prefix_grade_grades AS gg ON gg.userid = u.id
JOIN prefix_grade_items AS gi ON gi.id = gg.itemid AND gi.categoryid = gc.id
JOIN prefix_course_categories AS cc ON cc.id = c.category
LEFT JOIN prefix_scale AS s ON s.id = gi.scaleid
WHERE gi.courseid = c.id AND gi.itemname != 'Attendance' AND c.id = 141
%%FILTER_USERS:u.username%%
%%FILTER_STARTTIME:gg.timemodified:>%% %%FILTER_ENDTIME:gg.timemodified:<%%
ORDER BY gc.id, gi.idnumber