I asked in another forum before I realized that there was an analytics and reporting forum so I am asking here in hopes that someone can steer me in the right direction.
I need to run a report to find out how long it takes between the time a student submits any sort of human-gradable assignment and the time it is graded so that we can monitor our staff's efficiency. Right now I am going for a query similar to the one below. I still need to add in the actual assignments but I am not sure which items in moodle could possibly fit into the "submitted but waiting to be graded" category.
Which tables am I missing for this query and is there anything I am obviously doing wrong here? I am relatively new to moodle but not new to programing or SQL.
SELECT c.shortname AS course_name , cm.id AS cmid , cm.course AS courseid , md.name AS modname , gi.itemname AS itemname , u.firstname AS student_first , u.lastname AS student_last , f.firstname AS grader_first , f.lastname AS grader_last , NULLIF(GREATEST(IFNULL(g.overridden, 0) ,IFNULL(g.timecreated, 0) ,IFNULL(g.timemodified, 0)) ,0) AS graded_unixtimestamp FROM mdl_user u JOIN mdl_user_enrolments ue ON (u.id = ue.userid) JOIN mdl_enrol e ON (e.id = ue.enrolid) JOIN mdl_course c ON (c.id = e.courseid) JOIN mdl_course_modules cm ON (c.id = cm.course) JOIN mdl_modules md ON (md.id = cm.module) JOIN mdl_grade_items gi ON ( gi.itemmodule = md.name AND gi.iteminstance = cm.instance AND gi.courseid = cm.course) LEFT JOIN mdl_grade_grades g ON (gi.id = g.itemid AND g.userid = u.id) LEFT JOIN mdl_user f ON (g.usermodified = u.id)