Hi All,
Wondering where in SQL is stored student attempts data for ASSIGN (Assignment Module), I am creating a ad-hoc report to get student details, assessment grade, and number of attempts.
Example,
- username,firstname,lastname,assignment,grade,attempt
- 19000, John, Citizen, Assignment 1,100,3
here is my ad-hoc report EXCLUDING attempt count
____________________________
SELECT
u.username AS "Student ID",
u.firstname AS 'First Name' ,
u.lastname AS 'Last Name',
c.fullname AS 'Unit',
cc.name AS 'Course',
CASE
WHEN gi.itemtype = 'course'
THEN CONCAT( 'Course Total')
ELSE gi.itemname
END AS 'Activity Name',
CASE
WHEN gi.itemmodule = 'assign'
THEN CONCAT( 'Assessment Module')
ELSE CONCAT('Course')
END AS 'Activity Type',
CASE
WHEN ROUND(gg.finalgrade,2) < 3 AND gi.itemtype='course' THEN "NYC"
WHEN ROUND(gg.finalgrade,2) < 3 AND gi.itemtype='mod' THEN "NS"
WHEN ROUND(gg.finalgrade,2) = 3 AND gi.itemtype='course' THEN "C"
WHEN ROUND(gg.finalgrade,2) = 3 AND gi.itemtype='mod' THEN "S"
WHEN ISNULL(gg.finalgrade) AND gi.itemtype='course' THEN "NYC"
WHEN ISNULL(gg.finalgrade) AND gi.itemtype='mod' THEN "DNS"
ELSE CONCAT("Please check gradebook setup (",ROUND(gg.finalgrade,2),")")
END AS "Grade"
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_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
WHERE ((gi.courseid = c.id AND gi.itemmodule = "assign") OR (gi.courseid = c.id AND gi.itemtype = "course")) AND u.username REGEXP '^[0-9]+$'
ORDER BY u.username