Student Taking More than One Class In Moodle

I have tried to do some research but all the answers dont quite address my simple problem. In my Moodle Course database I do a SQL Query to see what student completed a class within a 24 hour period. This works well, except if a student completes TWO courses in a 24 hour period it only lists the first course course they took.
Is there a way I can have the report show ALL the courses a student took in a given period of time?

    SELECT 
         c.shortname AS 'Course Name', 
         u.lastname AS 'Last Name',
         u.firstname AS 'First Name', 
         u.email, 
         uid.data AS birthday, 

    DATE_FORMAT(FROM_UNIXTIME(p.timecompleted),'%m/%d/%Y %T') AS 'Completed Date'

    FROM 
         prefix_course_completions AS p
         JOIN prefix_course AS c ON p.course = c.id
         JOIN prefix_user AS u ON p.userid = u.id
         JOIN prefix_user_info_data AS uid ON uid.userid = u.id
         JOIN prefix_user_info_field AS uif ON uid.fieldid = uif.id

    WHERE  
         c.enablecompletion = 1
         and uif.shortname = 'birthday' 
         and from_unixtime(p.timecompleted) > 
         date_sub(now(), interval 1 day)

    GROUP BY u.username
    ORDER BY c.shortname

Answer

Try to remove your GROUP BY u.username. I don’t understand well how it behaves with moodle, but there is no room for it in a SQL query like yours.

Advertisement

Answer