Hello, 

I'm currently rebuilding an ad-hoc daily report that needs to track daily scorm and lesson completions. 

With the help of my developer colleagues we have sorted the scorm side. 

I've had a look at the schema but can anybody point me in the right directions where the completion data for the Lesson activity is found. 

As I've said this generates all the scorm but we just need to join the lesson: 

select u.institution as Pay_ref_no, c.shortname as xCOURSE_CODE,  c.fullname as Course_title,  

DATE_FORMAT(FROM_UNIXTIME( st.Timemodified ), '%Y%m%d' ) as Start_date,

 DATE_FORMAT(FROM_UNIXTIME( st.Timemodified ), '%Y%m%d' ) as End_date,

 DATE_FORMAT(FROM_UNIXTIME( st.Timemodified ), '%Y%m%d' ) as Req_date,

'F' as FAIL_I, 

 DATE_FORMAT(FROM_UNIXTIME( st.Timemodified ), '%Y%m%d %T' ) as "Date And Time", 

u.username

from mdl_role_assignments ra

join mdl_context ctx on ra.contextid = ctx.id

join mdl_course c on ctx.instanceid = c.id

join mdl_user u on ra.userid = u.id

join mdl_scorm s on c.id = s.course

join mdl_scorm_scoes_track st on s.id = st.Scormid and st.UserId= u.id and element = 'cmi.core.lesson_status'

where ctx.contextlevel = '50' and ra.roleid = '5' 

and c.category = 26 and u.deleted = 0 and c.id <> 100

and DATE_FORMAT(FROM_UNIXTIME( st.Timemodified ), '%Y-%m-%d' ) = CURDATE() - INTERVAL 1 DAY and st.value = 'passed'

Any help would be much appreciated. 

Thanks, 

Shea.