Also, my director asked me to get weekly reporting, and I just wanted to add what worked for me after looking at this post: https://stackoverflow.com/questions/22769986/how-to-get-last-week-date-range-based-on-current-date-in-sql
This code worked for me:
SELECT u.firstname AS 'First' , u.lastname AS 'Last', CONCAT(username) AS 'Display Name', c.fullname AS 'Course', cc.name AS 'Category', CASE
WHEN gi.itemtype = 'course'
THEN CONCAT(c.fullname, ' - Total')
ELSE gi.itemname
END AS 'Item Name',
ROUND(gg.finalgrade,2) AS Grade, FROM_UNIXTIME(gg.timemodified) AS TIME
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
FROM_UNIXTIME(gg.timemodified) between DATE_ADD(CURDATE(), INTERVAL -(5 + dayofweek(CURDATE())) DAY) and DATE_ADD(CURDATE(), INTERVAL -(dayofweek(CURDATE()) - 1) DAY)
AND gi.courseid = c.id AND gi.itemtype = 'course'
ORDER BY lastname
This code worked for me:
SELECT u.firstname AS 'First' , u.lastname AS 'Last', CONCAT(username) AS 'Display Name', c.fullname AS 'Course', cc.name AS 'Category', CASE
WHEN gi.itemtype = 'course'
THEN CONCAT(c.fullname, ' - Total')
ELSE gi.itemname
END AS 'Item Name',
ROUND(gg.finalgrade,2) AS Grade, FROM_UNIXTIME(gg.timemodified) AS TIME
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
FROM_UNIXTIME(gg.timemodified) between DATE_ADD(CURDATE(), INTERVAL -(5 + dayofweek(CURDATE())) DAY) and DATE_ADD(CURDATE(), INTERVAL -(dayofweek(CURDATE()) - 1) DAY)
AND gi.courseid = c.id AND gi.itemtype = 'course'
ORDER BY lastname