Student Taking More than One Class In Moodle

Question

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

Welcome to SO. There is pretty much no way we can answer this without seeing your data. — Andrew, May 25 '21 at 15:08
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. — James, May 25 '21 at 15:13
That was it! The GROUP BY u.username was restricting the other entries. Thank you so much! — DKeeler, May 25 '21 at 15:31

score 0 · Accepted Answer · answered May 25 '21 at 15:39

0

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.

answered May 25 '21 at 15:39

Connor Low

5,900
3
31
52

Student Taking More than One Class In Moodle

1 Answers1