I'm trying to write a report that shows, for a list of courses, whether they are ready for the start of the new term. At our college we copy existing courses forward into new shells for many instructors; for some courses, the instructor starts from a blank template. So I need to show columns for how many of certain kinds of activities and resources have been created for each course, and I also want to be able to show how many edits the assigned instructor has made to the course. I want to use this report just as a quick overview so I know if I need to contact an instructor and offer help.
I can write reports that summarize module contents, and I can write reports that summarize log information, but when I try to combine the two I get very strange results. Not all log entries show up, and module counts are distorted. My code is below (with just one example course in the list). Can anyone offer any suggestions for how to improve it?
SELECT
c.fullname AS Course
, CONCAT(u.lastname,', ', u.firstname) AS Teacher
, SUM( IF(cm.module = 9, 1,0)) AS Forums
, SUM( IF(cm.module = 16,1,0)) AS Quizzes
, SUM( IF(cm.module IN ('1','2'),1,0)) AS Assignments
, SUM( IF(cm.module = 15,1,0)) AS Pages
, SUM( IF(cm.module = 17,1,0)) AS Files
, SUM( IF(cm.module = 20,1,0)) AS Links
, COUNT( l.id) AS Edits
FROM prefix_course AS c
JOIN prefix_context AS ctx ON ctx.instanceid = c.id
JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id AND ra.roleid = 3
JOIN prefix_user AS u ON u.id = ra.userid
JOIN prefix_course_modules AS cm ON cm.course = c.id AND cm.visible = 1
LEFT JOIN prefix_log AS l ON l.userid = u.id AND l.course = c.id AND l.cmid = cm.id AND l.action NOT LIKE "view%"
%%FILTER_STARTTIME:l.TIME:>%% %%FILTER_ENDTIME:l.TIME:<%%
WHERE
c.shortname IN ('ENG510.1OL-FL13')
GROUP BY c.shortname, u.id
