SQL competenties per studieplan

Hieronder een sql die je kunt gebruiken met bijv. configurable reports om een rapportage te krijgen over de score van competenties van deelnemers per studieplan:

select cp.userid, cp.templateid, from_unixtime(cp.timecreated,'%d-%m-%Y'), u.lastname, u.firstname, ct.shortname, tc.competencyid
from test.mdl_competency_plan cp, test.mdl_user u, test.mdl_competency_template ct, test.mdl_competency_templatecomp tc
where cp.userid = u.id and cp.templateid = ct.id and cp.templateid = tc.templateid
order by cp.userid, cp.templateid, tc.competencyid;

select
    cp.userid,
    cp.templateid,
    from_unixtime(cp.timecreated,'%d-%m-%Y'),
    u.lastname,
    u.firstname,
    ct.shortname,
    tc.competencyid
from
    test.mdl_competency_plan cp,
    test.mdl_user u,
    test.mdl_competency_template ct,
    test.mdl_competency_templatecomp tc
where
    cp.userid = u.id and cp.templateid = ct.id and cp.templateid = tc.templateid
order by
    cp.userid,
    cp.templateid,
    tc.competencyid;