Currently, your report shows the grade each student received to any "grade item" they attempted to complete.
Grade items are anything in the Moodle which can assign a user a grade, most notably, quizzes (exams), and assignments.
Your report shows you the results of both quizzes and assignment, currently - which makes displaying the relevant dates a bit tricky:
Assignments have three defined dates:
'Allow submissions from' date - before this date the assignment will be unavailable to students
'Due' date - after this date passes, any submissions made to this assignment are considered "late"
'Cut-off' date - After this date the assignment will be unavailable to students
My guess is, for assignments, you'll be interested in the due date.
Now - for quizzes, you have just two dates to worry about:
'open the quiz' date and 'close the quiz' date, which determine the time period in which the students will be able to attempt the quiz.
Because of this difference, the dates for a grade item aren't stored in the prefix_grade_items table, but rather separately in the prefix_assign and prefix_quiz tables.
Now - to your question, my suggestion is:
SELECT
CONCAT(u.lastname , ' ' , u.firstname) 'DisplayName',
u.idnumber AS 'sztsz',
u.address AS 'cim',
u.institution AS 'osztaly',
u.department AS 'beosztas',
u.email,
u.lastip AS 'IP cim',
cc.name AS 'Kategoria',
c.fullname AS 'Kurzus',
CASE WHEN qz.timeclose>assignment.duedate
THEN FROM_UNIXTIME(qz.timeclose)
ELSE FROM_UNIXTIME(assignment.duedate)
END AS 'Date',
CASE WHEN gi.itemtype = 'Course'
THEN c.fullname + ' Course Total'
ELSE gi.itemname
END AS 'Vizsga', ROUND(gg.rawgrademax,0) AS elerheto, ROUND(gg.finalgrade,0) AS elert, ROUND(gg.finalgrade / gg.rawgrademax * 100 ,2) AS szazalek,
IF (ROUND(gg.finalgrade / gg.rawgrademax * 100 ,2) > 79,'Sikeres' , 'Sikertelen') AS Eredmeny
FROM prefix_course AS c
JOIN prefix_context AS ctx ON c.id = ctx.instanceid
JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id
JOIN prefix_user AS u ON u.id = ra.userid
JOIN prefix_grade_grades AS gg ON gg.userid = u.id
JOIN prefix_grade_items AS gi ON gi.id = gg.itemid
LEFT JOIN prefix_quiz AS qz ON qz.id=gi.iteminstance
LEFT JOIN prefix_assign AS assignment ON assignment.id=gi.iteminstance
JOIN prefix_course_categories AS cc ON cc.id = c.category
WHERE gi.courseid = c.id AND gi.itemname != 'Attendance' AND c.id = %%COURSEID%%
ORDER BY 'Name' ASC
This should add a "date" column to your report, which shows the close date for the grade item if it is a quiz, and the due date for the item if it is an assignment.
You will occasionally see the date 1.1.1970 - this signifies that no date was set for that activity.
You might also see on some occasions that the date field is empty, this means that that row contains a grade for a grade item that isn't a quiz, nor an assignment.
I might have completely misunderstood you - maybe you meant you want the completion date for the quiz and the submission date for assignments, rather than the closing and due date respectively. Let me know if this is the case, or better yet, try adjusting the query yourself - you may find this helpful:
http://www.examulator.com/er/
Good luck!
