Hello all, hopefully i can get some help.
I have the below report that is used to show assignments that have not been graded. The report is sorted and filtered in excel to get the information that I need. The only field missing is to show the Teacher that has been assigned to the class. Adding this field will make it much easier to advise each teacher of the outstanding assignments they have to grade. Currently I can only work that out by CourseID.
I have attached a screenshot which shows the fields as they are currently, any help would be greatly appreciated. We run PostgreSQL.
SELECT cm.id as assignmentid
,cm.course AS courseid
,mas.userid
,c.shortname as courseshortname
,c.fullname as coursename
,u.firstname
,u.lastname
,u.username
,ma.name as assignmentname
,mas.status
,TO_CHAR(TO_TIMESTAMP(mas.timecreated::integer), 'DD/MM/YYYY') as datecreated
,TO_CHAR(TO_TIMESTAMP(mas.timemodified::integer), 'DD/MM/YYYY') as datemodified
,mag.grade
FROM prefix_course_modules cm
inner join prefix_modules m on cm.module=m.id
inner join prefix_assign ma on ma.id=cm.instance
inner join prefix_assign_submission mas on mas.assignment=cm.instance
left outer join prefix_assign_grades mag on mag.assignment=mas.assignment and mag.userid=mas.userid
inner join prefix_user u on u.id=mas.userid
inner join prefix_course c on c.id=cm.course
where m.name='assign'
and (mag.grade <0 or mag.grade is null)
order by cm.instance
