Hi there!
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?
select
asub.assignment,
asub.userid,
student.username,
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.status,
asub.attemptnumber + 1 'Attempt #',
case when asub.latest = 0
then 'no'
else 'yes'
end 'latest attempt?'
from mdl_assign_submission asub
join mdl_user student on student.id = asub.userid
order by
asub.assignment asc,
asub.userid asc,
asub.attemptnumber asc
Output: 