Create report with total time per sections scorms

Create report with total time per sections scorms

by Pantelis Toptsis -
Number of replies: 2

I have a course that has 1 or more scorm packages in each section. The report must sum total time spent in all scorms in each section. 

For example 

Section name | Total time in section
unit title| 7:15"32 

 Can you please help me in this?

Average of ratings: -
In reply to Pantelis Toptsis

Απάντηση: Create report with total time per sections scorms

by Pantelis Toptsis -

So far I tried to do 

SELECT u.id idname, c.fullname Course, s.name name, STR_TO_DATE(st.VALUE,'PT%iM%sS') Total,st.element, cm.module, s.id, cm.visible 

FROM prefix_scorm_scoes_track st 

inner JOIN prefix_user AS u ON st.userid=u.id

INNER JOIN prefix_scorm as sc ON st.scormid=sc.id

INNER JOIN prefix_course AS c ON c.id=sc.course

INNER JOIN prefix_course_sections AS s ON s.course=c.id 

INNER JOIN prefix_course_modules AS cm ON cm.course=c.id 

WHERE st.ELEMENT="cmi.total_time" AND s.visible='1' AND cm.module=18 AND STR_TO_DATE(st.VALUE,'PT%iM%sS') is not null

GROUP BY s.id

ORDER BY s.id, c.fullname, s.name

But the results are not correct.


Any suggestions?

Average of ratings: -
In reply to Pantelis Toptsis

Απάντηση: Create report with total time per sections scorms

by Pantelis Toptsis -
I changed my query. It shows results but I also get empty entries an multiple apperances of some entries

Can I get some help please?

SELECT s.name SECTION, STR_TO_DATE(st.VALUE,'PT%iM%sS') Total, st.scormid Scorm_ID, st.userid
FROM prefix_course_sections AS s
JOIN prefix_scorm AS sc ON s.course=sc.course
JOIN prefix_scorm_scoes_track AS st ON st.scormid=sc.id
WHERE st.ELEMENT="cmi.total_time" AND st.userid=%%USERID%% AND sc.course=%%COURSEID%%
Average of ratings: -