Modify Ad-Hoc db Query to include a specific date range

Modify Ad-Hoc db Query to include a specific date range

by JR RT -
Number of replies: 4

  • Downloaded the ad-hoc contributed reports plugin
  • Added 2.6.2 Site-Wide Grade Report with Just Course Totals query
  • Tested and ran a successful query with the Query SQL listed in Moodle Docs
However, I want to just have the site wide course totals that were completed this month (from beginning of month to whatever the current day is that I run the report near the end of the month).

  • I tried looking up the MySQL code for a date range in Moodle Docs, was unsuccessful 
  • Searched: dev.mysql.com/doc/ , stackoverflow , and github , but I didn't understand as I'm not a coder

The code I found that seems like it would perform the function I am looking for is: 

BETWEEN DATE_FORMAT(NOW() - INTERVAL 1 MONTH, '%Y-%m-01 00:00:00') AND DATE_FORMAT(LAST_DAY(NOW() - INTERVAL 1 MONTH), '%Y-%m-%d 23:59:59')

But I don' know if that is the right instruction or format or anything.


What could I add to the listed code that would just have the totals for the specific date range I mentioned?

SELECT u.firstname AS 'First' , u.lastname AS 'Last', u.firstname + ' ' + u.lastname AS 'Display Name',
cc.name AS 'Category',
CASE
  WHEN gi.itemtype = 'course'
   THEN c.fullname + ' Course Total'
  ELSE gi.itemname
END AS 'Item Name',

ROUND(gg.finalgrade,2) AS Grade,
DATEADD(ss,gg.timemodified,'1970-01-01') 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  gi.courseid = c.id AND gi.itemtype = 'course'

ORDER BY lastname

Thanks!

Average of ratings: Useful (1)
In reply to JR RT

Re: Modify Ad-Hoc db Query to include a specific date range

by Randy Thornton -
Picture of Documentation writers
The only item of data in the query that is date related is gg.timemodified, the time of the grading.

So, you need to add your data limitation logic to your WHERE clause to get the gg.timemodified that is in this same month. However, you don't need any of the formatting to due the date calculation at all because you are not showing them to the user in the report results: just do the straight comparison. One good way to do this is explained here: https://stackoverflow.com/questions/11808232/how-do-i-select-between-the-1st-day-of-the-current-month-and-current-day-in-mysq.

Remember, all dates and times in Moodle are saved as timestamps. It is better and quicker to do the math on the timestamps themselves instead of having to convert them to some format or another when you can.
Average of ratings: Useful (3)
In reply to Randy Thornton

Re: Modify Ad-Hoc db Query to include a specific date range

by JR RT -
Randy,

I updated the WHERE clause to:
WHERE
FROM_UNIXTIME(gg.timemodified) between DATE_FORMAT(NOW() ,'%Y-%m-01') AND NOW()
AND gi.courseid = c.id AND gi.itemtype = 'course'

And it works for me! Thank you for the info
Average of ratings: Useful (1)
In reply to JR RT

Re: Modify Ad-Hoc db Query to include a specific date range

by Randy Thornton -
Picture of Documentation writers
That's a very cool trick with the formatting smile DATE_FORMAT(NOW() ,'%Y-%m-01')
Average of ratings: -
In reply to JR RT

Re: Modify Ad-Hoc db Query to include a specific date range

by JR RT -
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
Average of ratings: Useful (3)