Hi Moodle Community!
I was hoping someone could help me close the final gaps I have with my SQL scripting.
With this script:
SELECT u.lastname AS 'Last Name',u.firstname AS 'First Name' , u.username As 'Username', u.email AS 'Email', cc.name AS 'Category', c.shortname AS 'Course',
gi.itemname AS 'Activity',
if (ROUND(gg.finalgrade / gg.rawgrademax * 100 ,2) > 79,'Yes' , 'No') as 'Activity Completed', DATE_FORMAT( FROM_UNIXTIME( gg.timemodified ) , 'Completed %m-%d-%Y at %m:%s%p' ) as 'Activity Completion 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.itemname != 'Attendance'
ORDER BY `Last Name`, `Category`, `Course`, `Activity` ASC
I can generate a sheet that contains:Last NameFirst NameUsernameEmailCategoryCourse TitleActivity Title
Activity Completed?Activity Completion Time
I am not as skilled with SQL and could really use a hand with scripting a few additional queries into my code.
I would like to generate a report using the ad hoc reporting plugin to create this kind of spreadsheet:
- Last Name
- First Name
- Course Title
- Course Completion Date
- Total Time in Course
- Times Accessed
- Activity Title
- Time Spent in Activity
- Activity First Started Time
- Activity Completion Time
Can someone help me? Please?