Hi everyone,
We have been using the following SQL to pull a report showing all course completions for all users on our site so we can export this information to our student information system (SAP). The report, as you can imagine, is getting longer and longer as more completions are added.
SELECT u.idnumber, u.firstname, u.lastname, c.fullname as course, FROM_UNIXTIME(com.timecompleted-(0*60*60), '%M %d, %Y') as coursecompleted, FROM_UNIXTIME(max(st.timemodified)-(0*60*60), '%M %d, %Y') as scormcompleted
FROM prefix_scorm_scoes_track st
JOIN prefix_scorm s ON s.id = st.scormid
JOIN prefix_course c ON c.id = s.course
JOIN prefix_user u ON u.id = st.userid
JOIN prefix_course_completions com ON com.course = c.id AND com.userid = u.id
WHERE st.element = 'cmi.core.lesson_status' AND (st.value = 'completed' OR st.value = 'passed') AND com.timecompleted IS NOT NULL
GROUP BY u.idnumber, u.firstname, u.lastname, scormid, c.fullname, com.timecompleted
I'd like to offer my coordinators two report options from here on out:
1) The report with the information from the original query but limited to just the completions for the previous 30 days. We pull the report weekly so having a rolling 30 days would give us enough overlap to catch errors.
2) The full report from the original query but that doesn't display the table in HTML in Moodle; it would just give us the option to download the csv.
I don't know SQL at all and someone else wrote the code for us originally. Can anyone help me make the modifications?
Thank you so much in advance!
Leah Hemeon