Attendance Sql Querry

Attendance Sql Querry

by Dorel Manolescu -
Number of replies: 2
Picture of Plugin developers
Hy,
I am trying to make a report with all the users which attended the face to face sessions for all the system. I did this querry and it returns me some results, but i really don t know if those are the good ones.
This is the querry:

SELECT DISTINCT u.id AS userid, s.id AS submissionid, u.firstname, u.lastname, u.email, f.id AS facetofaceid, f.course AS courseid, gg.finalgrade AS presence, s.sessionid AS idsesiune
FROM mdl_facetoface f
JOIN mdl_facetoface_submissions s ON s.facetoface = f.id
JOIN mdl_user u ON u.id = s.userid
JOIN mdl_grade_grades gg ON gg.userid = s.userid
WHERE s.timecancelled =0
GROUP BY s.sessionid, u.firstname
ORDER BY s.sessionid

Can anyone tell me if it is correct?

How the face to face tables are related to the grade_grades table (one of the fieds is for sure userid, but there are other linked fileds? could itemid be one of them )

Thank you in advance!
Average of ratings: -
In reply to Dorel Manolescu

Re: Attendance Sql Querry

by Dorel Manolescu -
Picture of Plugin developers
Hy,
We made a few modifications to the initial querry.
We hope this time is the good one. If anyone can test it please give us a feedback.

SELECT
cName, sDate, sID, fName, lName, eMail, presence
FROM
(SELECT
a.fullname as cName, FROM_UNIXTIME(d.timestart) as sDate, d.sessionid as sID, f.firstname as fName , f.lastname as lName, f.email as eMail, 1 as presence
FROM
mdl_course a JOIN mdl_facetoface b ON a.id = b.course
JOIN mdl_facetoface_sessions c ON b.id = c.facetoface
JOIN mdl_facetoface_sessions_dates d ON c.id = d.sessionid
JOIN mdl_facetoface_submissions e ON c.id = e.sessionid
JOIN mdl_user f ON e.userid = f.id
JOIN mdl_grade_grades gg ON f.id = gg.userid
WHERE
e.timecancelled=0
GROUP BY d.sessionid, e.userid
UNION
SELECT
a.fullname as cName, FROM_UNIXTIME(d.timestart) as sDate, d.sessionid as sID, f.firstname as fName , f.lastname as lName, f.email as eMail, 0 as presence
FROM
mdl_course a JOIN mdl_facetoface b ON a.id = b.course
JOIN mdl_facetoface_sessions c ON b.id = c.facetoface
JOIN mdl_facetoface_sessions_dates d ON c.id = d.sessionid
JOIN mdl_facetoface_submissions e ON c.id = e.sessionid
JOIN mdl_user f ON e.userid = f.id
WHERE
e.timecancelled=0 GROUP BY d.sessionid, e.userid) as rez
GROUP BY cName, lName

Thank you!
ps. : of course 1 presence, 0 absence

Average of ratings: -
In reply to Dorel Manolescu

Re: Attendance Sql Querry

by Dorel Manolescu -
Picture of Plugin developers
An other version. Hope the good one:

SELECT
cName, sDate, sID, fName, lName, eMail, uID, presence
FROM
(SELECT
gi.itemname as cName,
FROM_UNIXTIME(fd.timestart) as sDate,
s.id as sID,
us.firstname as fName,
us.lastname as lName,
us.email as eMail,
e.userid as uID,
1 as presence
FROM
mdl_facetoface f,
mdl_facetoface_sessions s,
mdl_facetoface_submissions e,
mdl_grade_grades gg,
mdl_grade_items gi,
mdl_facetoface_sessions_dates fd,
mdl_course_categories ca,
mdl_course cs,
mdl_user us
where
gi.id=gg.itemid and
gi.courseid=f.course and
f.id=s.facetoface and
f.id=e.facetoface and
gg.userid=e.userid and
s.id=e.sessionid and
gi.itemtype="mod" and
s.id = fd.sessionid and
gg.userid=us.id and
cs.id = gi.courseid and
cs.category = ca.id and
gg.finalgrade=100.00000 and
e.timecancelled=0
GROUP BY e.sessionid, gg.userid
UNION
SELECT
a.fullname as cName, FROM_UNIXTIME(d.timestart) as sDate, d.sessionid as sID, f.firstname as fName , f.lastname as lName, f.email as eMail, e.userid as uID, 0 as presence
FROM
mdl_course a JOIN mdl_facetoface b ON a.id = b.course
JOIN mdl_facetoface_sessions c ON b.id = c.facetoface
JOIN mdl_facetoface_sessions_dates d ON c.id = d.sessionid
JOIN mdl_facetoface_submissions e ON c.id = e.sessionid
JOIN mdl_user f ON e.userid = f.id
WHERE
e.timecancelled=0 GROUP BY d.sessionid, e.userid) as rez
GROUP BY sID, uID
ORDER BY cName, sDate, uID
Average of ratings: -