AnsweredAssumed Answered

How to add to my Last Access query the grades in grade center for students

Question asked by fs0070186 on Sep 12, 2018
Latest reply on Sep 20, 2018 by chris.bray

Hi all,

  I need some help to solve my query.

  I have a query where I get the last access for my students to do a dashboard, I need to add all the grades for those students to create a unic table.

  Can anybody help me to add the right tables and joins?

Thanks,

   Fede

This is my query.

set scape '\';

SELECT

lastname, firstname, user_id, bbdcourse, FECHAINSCRP, lastaccess, CURRENT_DATE as fecha_captura,rol

FROM (SELECT

t.name term_name,

u.lastname, u.firstname, u.user_id,

cm.course_id AS BBDCOURSE,

cu.enrollment_date as FECHAINSCRP,

cu.last_access_date AS LASTACCESS,

cmchild.course_id AS CHILDCOURSE,

cu.role as ROL,

(CASE WHEN cmchild.course_id IS NULL THEN cm.course_id ELSE cmchild.course_id END) LORACOURSE,

(CASE WHEN cmchild.course_id IS NULL THEN cm.course_id || u.user_id ELSE cmchild.course_id || u.user_id END) SEARCHKEY

FROM users u

LEFT JOIN course_users cu ON cu.users_pk1 = u.pk1

LEFT JOIN course_course cc ON cc.crsmain_pk1 = cu.crsmain_pk1

LEFT JOIN course_main cm ON cm.pk1 = cu.crsmain_pk1

LEFT JOIN course_main cmchild ON cmchild.pk1 = cu.child_crsmain_pk1

LEFT JOIN course_main cmparent ON cmparent.pk1 = cc.crsmain_parent_pk1

LEFT JOIN course_term ct ON ct.crsmain_pk1 = cu.crsmain_pk1

LEFT JOIN term t ON t.pk1 = ct.term_pk1

WHERE cmparent.pk1 IS NULL) SQ1

WHERE bbdcourse LIKE ( 'XXXXX%\_YYY%') and rol in ('S', 'P') and user_id not like('ZZZZ%')

ORDER BY bbdcourse, lastname, firstname, user_id;

Outcomes