-- GROUP_CONCAT lists all the weeks and the standards completed in that week in a single table cell -- By adding a order by in the group concat, we get the proper ordering of weeks (weeks were in the incorrect order without it) -- CHAR(63) because the '?' is not accepted by Moodle Ad-Hoc Queries. SELECT outcomes.courseid, CONCAT('', C.fullname,'' ) as course_name, CONCAT( GROUP_CONCAT( CONCAT(outcomes.weekstr, concat(' [', outcomes.count, ']')) ORDER BY courseid ASC, w ASC SEPARATOR ' ' ), ' Total:', SUM(outcomes.count) ) as outcomes_per_week FROM ( -- This sub query combines the three queries used by Moodle to get the outcome count -- The WHERE statement -- a) gets the day 16 weeks ago -- b) We want to include results beginning on Sunday of that week -- The SELECT weekstr - each day belongs to a week. We match each day with the start of its week. -- eg. For the week of Wed Jan 5, the weekstr would be Sun-2 SELECT goc.courseid, COUNT(gg.finalgrade) AS count, DATE_FORMAT( str_to_date( concat(year(from_unixtime(gg.timemodified)), LPAD(week(from_unixtime(gg.timemodified)), 2, 0), 'sunday' ), '%X%V %W'), '%b-%e') weekstr, week(from_unixtime(gg.timemodified)) as w FROM prefix_grade_outcomes go JOIN prefix_grade_outcomes_courses goc ON go.id = goc.outcomeid JOIN prefix_grade_items as gi ON goc.outcomeid = gi.outcomeid AND gi.courseid = goc.courseid JOIN prefix_grade_grades as gg ON gg.itemid = gi.id WHERE gg.timemodified >= UNIX_TIMESTAMP(STR_TO_DATE(DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 16 week), '%Y%V Sunday'), '%X%V %W')) GROUP BY courseid, week(from_unixtime(gg.timemodified)) ) as outcomes JOIN prefix_course as C ON C.id = outcomes.courseid GROUP BY courseid ORDER BY C.fullname asc
Note: Optimization can be done to move the weekstr to the outside query.