Question 20594 in Mysql Question posted in
The official MySQL documentation can be found here.

sql query to get the status of scorm completion – Mysql

i have to get the status of completion of the scrom package by students and i wrote a code that is displaying only the completed and incomplete students data, but i also want those students who are enrolled but not attempted the thing, help me.

This is my query:

select u.id as userid,u.firstname as firstname,u.lastname as lastname,c.fullname as coursename,s.name as scormname,sst.attempt as attempt,sst.value as status,
    CASE
        when s.timeopen=0 then 'not enabled'
        when s.timeopen<>0 then from_unixtime(s.timeopen,'%d-%b-%Y') 
    end 
    as starttime,
    CASE
        when s.timeclose=0 then 'not enabled'
        when s.timeclose<>0 then from_unixtime(s.timeclose,'%d-%b-%Y') 
    end 
    as enddate
from mdl_scorm_scoes_track sst

join mdl_scorm s on s.id=sst.scormid

join mdl_course c on c.id=s.course

join mdl_user u on sst.userid=u.id

where sst.value in ('incomplete','completed')

–give me some code that will be helpful

Tags:

2

Answers


  1. 0

    If I understand correctly, I think you need Left Join and starting from the user table

    select u.id as userid,u.firstname as firstname,u.lastname as lastname,c.fullname as coursename,s.name as scormname,sst.attempt as attempt,sst.value as status,
    CASE
        when s.timeopen=0 then 'not enabled'
        when s.timeopen<>0 then from_unixtime(s.timeopen,'%d-%b-%Y') 
    end 
    as starttime,
    CASE
        when s.timeclose=0 then 'not enabled'
        when s.timeclose<>0 then from_unixtime(s.timeclose,'%d-%b-%Y') 
    end 
    as enddate
from mdl_user u

left join mdl_scorm_scoes_track sst on sst.userid=u.id

left join mdl_scorm s on s.id=sst.scormid

left join mdl_course c on c.id=s.course

where c.fullname IS NOT NULL -- Make sure the user is enrolled in the course
    AND (sst.value in ('incomplete','completed') OR sst.value IS NULL)
    Login or Signup to reply.
  2. 0

    The activity completion is in a table called mdl_course_modules_completion

    This SQL narrows the completion down to scorm activities

    Also a check to see if a user is enrolled in any way – note that a user can be enrolled more than once on a course

    SELECT u.id AS userid, u.firstname, u.lastname,
    c.id AS courseid, c.fullname AS coursename,
    s.id AS scormid, s.name AS scormname,
    CASE
        WHEN cm.completion = 0 THEN 'None' /* COMPLETION_TRACKING_NONE */
        WHEN cm.completion = 1 THEN 'Manual self completion' /* COMPLETION_TRACKING_MANUAL */
        WHEN cm.completion = 2 THEN 'Automatic when conditions are met' /* COMPLETION_TRACKING_AUTOMATIC */
    END AS completiontype,
    CASE
       WHEN cmc.completionstate = 0 THEN 'Incomplete' /* COMPLETION_INCOMPLETE */
       WHEN cmc.completionstate = 1 THEN 'Completed' /* COMPLETION_COMPLETE */
       WHEN cmc.completionstate = 2 THEN 'Completed with Pass' /* COMPLETION_COMPLETE_PASS */
       WHEN cmc.completionstate = 3 THEN 'Completed with Fail' /* COMPLETION_COMPLETE_FAIL */
       WHEN cmc.completionstate = 4 THEN 'Completed with Record of Prior Learning' /* COMPLETION_COMPLETE_RPL */
       ELSE 'Unknown'
    END AS completionstate,
    cmc.timemodified
FROM mdl_course_modules_completion cmc
JOIN mdl_user u ON u.id = cmc.userid
JOIN mdl_course_modules cm ON cm.id = cmc.coursemoduleid
JOIN mdl_course c ON c.id = cm.course
JOIN mdl_modules m ON m.id = cm.module AND m.name = 'scorm' /* Scorm activities only */
JOIN mdl_scorm s ON s.id = cm.instance
WHERE EXISTS (
    /* User is enrolled */
    SELECT ue.id
    FROM mdl_enrol e
    JOIN mdl_user_enrolments ue ON ue.enrolid = e.id AND ue.userid = u.id
    WHERE e.courseid = c.id
)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search