Good morning,
I have a SQL report that should list grades for all activities, mostly assignments in a course, even ungraded assignments. It works well for most children. For some students though, it does not list activities that have not been graded. I am having to put a space in the feedback columns to force these to show. Is there some setting that is causing this behavior? Here is the SQL code:
SELECT
gc.fullname AS 'Category' ,
gi.itemname 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 ROUND(gg.finalgrade,0)
END AS 'Assessment',
gg.feedback AS 'Feedback Comments' ,
CONCAT(u.FirstName,' ',u.MiddleName,' ',u.LastName) As 'Name' ,
(select DATE_FORMAT(DATE_ADD(FROM_UNIXTIME(i.data), INTERVAL +0 DAY), '%d/%m/%Y')
from prefix_user_info_data i
join prefix_user_info_field f on i.fieldid = f.id
WHERE i.userid = u.id
AND f.shortname = 'DOB') As 'DOB' ,
(select DATE_FORMAT(DATE_ADD(FROM_UNIXTIME(i.data), INTERVAL +0 DAY), '%d/%m/%Y')
from prefix_user_info_data i
join prefix_user_info_field f on i.fieldid = f.id
WHERE i.userid = u.id
AND f.shortname = 'admissiondate') 'DateOfAdmission'
#, gi.idnumber AS 'ItemID'
# Note that a 1 is added to DOB and admission dates. This is only needed if
# generating the report from USA (?). You should add 0 if generating report from India!
#
FROM prefix_course AS c
JOIN prefix_context AS ctx ON c.id = ctx.instanceid AND c.id = 320 AND ctx.contextlevel=50
JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id AND ra.roleid=5
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
JOIN prefix_scale AS s ON s.id = gi.scaleid
#
#
WHERE gi.courseid = c.id AND gi.itemname != 'Attendance' AND c.id = 320
#%%FILTER_USERS:u.username%%
%%FILTER_SEARCHTEXT:u.idnumber:~%%
#
ORDER by gi.idnumber ASC