I've been tasked in creating a SQL query (for use w/ the Configurable Reports plugin) that pulls all Assignment submission attempts for all assignments in courses in a particular category path and show the feedback provided, timestamps for attempt submitted, attempt edited, attempt graded, attempt grade modified. I have created the below query to get the assignment submission information, but I cannot for the life of me figure out how to link the feedback comments (listed in mdl_grades_grades_history to the attempts in mdl_assign_submission. How can I do this?
concat(student.firstname, ' ', student.lastname) 'Student Name',
from_unixtime(asub.timecreated, '%H:%i %D %M %Y') 'Attempt Created',
from_unixtime(asub.timemodified, '%H:%i %D %M %Y') 'Attempt Last Edited',
asub.attemptnumber + 1 'Attempt #',
case when asub.latest = 0
end 'latest attempt?'
from mdl_assign_submission asub
join mdl_user student on student.id = asub.userid