SQL query

SQL query

by Alejandro Cemylto -
Number of replies: 18

Hi Juan and everybody else,

could someone tell me what i am doing wrong here!! it's driving me nutts smile

_____________

SELECT u.firstname AS 'First' , u.lastname AS 'Last', u.firstname + ' ' + u.lastname AS 'Display Name',
cc.name AS 'Category',
CASE
WHEN gi.itemtype = 'course'
THEN c.fullname + ' Course Total'
ELSE gi.itemname
END AS 'Item Name',

ROUND(gg.finalgrade,2) AS Grade,
DATEADD(ss,gi.timemodified,'1970-01-01') AS Time

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
JOIN prefix_course_categories AS cc ON cc.id = c.category

WHERE gi.courseid = c.id AND gi.itemtype = 'course'

ORDER BY lastname

___________________________________________________

This is supposed to be a Site-Wide Grade Report with Just Course Totals, i found it on the ad-hoc. it says MySQL users, you'll need to use the MySQL DATE_ADD function instead of DATEADD:DATE_ADD('1970-01-01', INTERVAL gi.timemodified SECONDS) AS Time

Tried them both and it still won't work.

Thank you

Average of ratings: -
In reply to Alejandro Cemylto

Re: New plugin: Custom reports

by Nico Potgieter -

This what I use. Also see http://www.w3schools.com/sql/func_date_format.asp for formatting.

SELECT u.username As EmpNo, u.firstname AS 'Name' , u.lastname AS 'Surname',cc.name AS 'Category', c.fullname AS 'Course',
gi.itemname AS 'Item Name', ROUND(gg.finalgrade,2) AS Score,ROUND(gg.rawgrademax,2) AS Max, ROUND(gg.finalgrade / gg.rawgrademax * 100 ,2) as '%',


 if (ROUND(gg.finalgrade / gg.rawgrademax * 100 ,2) > 79,'Yes' , 'No') as Pass, DATE_FORMAT( FROM_UNIXTIME( gg.timemodified ) , '%d %b %Y %T' ) as 'Date Complete'


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
JOIN prefix_course_categories AS cc ON cc.id = c.category
WHERE  gi.courseid = c.id and gi.itemname != 'Attendance' %%FILTER_CATEGORIES:cc.id%%  %%FILTER_COURSES:c.id%%

ORDER BY `EmpNo` , `Category` ASC

Average of ratings: -
In reply to Nico Potgieter

Re: New plugin: Custom reports

by Alejandro Cemylto -

Thanks Nico, unfortunately it turned out not what i am looking for.

this one is exactly what i am looking for but it's missing the Course Final Grade Column. can you please assist and add the line to accomplish this?

______________________________________________________________

SELECT co.fullname, u.username, u.country, u.firstname, u.lastname, FROM_UNIXTIME(ra.timemodified,'%D %M %h:%i:%s %x') AS enrol_time FROM prefix_role_assignments ra, prefix_user u, prefix_context c, prefix_course co WHERE c.id = ra.contextid AND c.contextlevel = 50 AND ra.userid = u.id AND c.instanceid = co.id AND ra.timemodified >0 AND u.deleted = 0

%%FILTER_COURSES:co.id%%
%%FILTER_STARTTIME:ra.timemodified:>%% %%FILTER_ENDTIME:ra.timemodified:<%%
%%FILTER_USERS:u.country%%

 

Average of ratings: -
In reply to Alejandro Cemylto

Re: New plugin: Custom reports

by Martin Contreras -

Hi,

Use this 

DATE_FORMAT( FROM_UNIXTIME( fp.modified ) , '%Y/%m/%d' ) AS date
Average of ratings: -
In reply to Alejandro Cemylto

Re: New plugin: Custom reports

by Nico Potgieter -

Ok try this. Keep in mind I am not a SQL developer

SELECT c.fullname As 'Course Fullname',
u.username as 'Username',
u.country As 'Country',
u.firstname As 'Firstname',
u.lastname as 'Last Name',
ccc.gradefinal AS 'Final Grade',
DATE_FORMAT(FROM_UNIXTIME(ccc.timecompleted),'%D %M %h:%i:%s %x') AS 'Date Completed'

FROM prefix_course_completion_crit_compl AS ccc
JOIN prefix_course AS c ON  c.id = ccc.course
JOIN prefix_context AS ctx ON c.id = ctx.instanceid
JOIN prefix_user AS u ON u.id = ccc.userid

%%FILTER_COURSES:c.id%%
%%FILTER_STARTTIME:ra.timemodified:>%% %%FILTER_ENDTIME:ra.timemodified:<%%
%%FILTER_USERS:u.country%%

Average of ratings: -
In reply to Nico Potgieter

Re: New plugin: Custom reports

by Alejandro Cemylto -

Not working! it's giving me

SQL QueryRequired field
Query failed
Average of ratings: -
In reply to Alejandro Cemylto

Re: New plugin: Custom reports

by Alejandro Cemylto -

Basically all i need is to see Enrolments in courses for all the users with course grades and time enrolled colums, and a simple start End date filter.  Anyone good at the SQL here please assist!

Much appreciated.

Ale

Average of ratings: -
In reply to Alejandro Cemylto

Re: New plugin: Custom reports

by Juan Leyva -
Picture of Core developers Picture of Moodle HQ Picture of Particularly helpful Moodlers Picture of Plugin developers Picture of Testers

See:

http://docs.moodle.org/21/en/ad-hoc_contributed_reports

Hope you find a useful query

Average of ratings: -
In reply to Juan Leyva

Re: New plugin: Custom reports

by Juan Leyva -
Picture of Core developers Picture of Moodle HQ Picture of Particularly helpful Moodlers Picture of Plugin developers Picture of Testers

This query works for MySQL

Display the user name, courses and final grade in each course

SELECT u.firstname AS 'Firstname', u.lastname AS 'Lastname', c.shortname AS Curso, ROUND( gg.finalgrade, 0 ) AS 'Final grade'
FROM prefix_course c
JOIN prefix_context ctx ON c.id = ctx.instanceid
JOIN prefix_role_assignments ra ON ra.contextid = ctx.id
JOIN prefix_user u ON u.id = ra.userid
JOIN prefix_grade_grades gg ON gg.userid = u.id
JOIN prefix_grade_items gi ON gi.id = gg.itemid
JOIN prefix_course_categories cc ON cc.id = c.category
WHERE gi.courseid = c.id
AND gi.itemtype = 'course'
ANd u.deleted = 0
ORDER BY gg.finalgrade DESC

Average of ratings: -
In reply to Juan Leyva

Re: New plugin: Custom reports

by Chris Mayo -

First off, I love this report. It was a great starting point for what I am trying to do. I've been trying to modify it to include a date column but that's not been going so well, and I'm not exactly well-versed in SQL. And I should mention that I'm running SQL 2008 rather than MySQL. What I have right now is:

SELECT
u.firstname AS 'Firstname',
u.lastname AS 'Lastname',
u.firstname + ' ' + u.lastname AS 'Display Name',
c.shortname AS Course,  
cc.name AS 'Category',
ROUND( gg.finalgrade, 0 ) AS 'Final grade'

FROM prefix_course c
JOIN prefix_context ctx ON c.id = ctx.instanceid
JOIN prefix_role_assignments ra ON ra.contextid = ctx.id
JOIN prefix_user u ON u.id = ra.userid
JOIN prefix_grade_grades gg ON gg.userid = u.id
JOIN prefix_grade_items gi ON gi.id = gg.itemid
JOIN prefix_course_categories cc ON cc.id = c.category
WHERE gi.courseid = c.id
AND gi.itemtype = 'course'
ANd u.deleted = 0
ORDER BY lastname ASC, Course ASC

What I would love to get is a date column on the report showing the date of course completion as mm/dd/yyyy. I've tried a number of different snippets of code from other reports but they all seem to generate errors such as:

ROUND(gg.finalgrade,2) AS Grade,
DATEADD(ss,gi.timemodified,'1970-01-01') AS Time

I tried that with and without the ROUND statement since the aforementioned code already had it in there. I've also experimented with the CONVERT() function with no success either. Any ideas???

 

Thanks!!!!

Average of ratings: -
In reply to Chris Mayo

Re: New plugin: Custom reports

by Warren H -

HI to all

This is a great plug-in

I'm looking for a query that shows all my students in a course with their teacher's name, their attendance rate and in which group they are. I have seperate groups.

I have checked the ad-hoc contributed reports, but could not find a suitable SQL

Also, when I create a user report and select attendance on user module actions or user module outline stats, I get a blank screen. Everything else in conf report works.

Thanks in advance.

Warren

Average of ratings: -
In reply to Juan Leyva

Re: New plugin: Custom reports

by John Oleary -

Hi Juan,

Thanks so much for this module! It's just exactly what i need. This bit of SQL you posted is almost everything i need for my own report, name, chourse, final grade, etc. but i'm missing one thing, i'm trying to also add the field for 'date certificate was issued' which is prefix_certificate_issues.timecreated.

i'm able to build a query that posts it along with the user id and format it as 'regular' time, ie:

DATE_FORMAT( FROM_UNIXTIME( mdl_certificate_issues.timecreated ) AS 'Date of Certificate'

But i'm stuck when it comes to integrating it into your code snippet above, i think i'm not getting my head around how to JOIN it so that the query pulls up the corresponding entry in .timecreated with the user's id.

Any help would be hugely appreciated! Thanks again.

 

Average of ratings: -
In reply to John Oleary

Custom reports

by Sophia Ben -

Hello,

 

what sql can I use to pull letter grades from a course that has a letter scale added onto a sitewide grade report?

Average of ratings: -
In reply to Alejandro Cemylto

Odg: SQL query

by Mitja Decman -

Try this:

DATE_FORMAT(DATE_ADD('1970-01-01', INTERVAL gi.timemodified SECOND),'%d.%c.%Y-%T') AS Time

or just

DATE_ADD('1970-01-01', INTERVAL gi.timemodified SECOND) AS Time

Average of ratings: -
In reply to Mitja Decman

Re: Odg: SQL query

by roc mehra -

Hi All

I need to get total hits accross my site course wise from x date to y date. How can i get this?

When i use

SELECT COUNT(l.id) hits, l.course courseId, c.fullname coursename
FROM mdl_log l INNER JOIN mdl_course c ON l.course = c.id  
GROUP BY courseId
ORDER BY hits DESC

It gives me all hits. But i need according to some time then i have used this one

SELECT COUNT(l.id) hits, l.course courseId, c.fullname coursename
FROM mdl_log l INNER JOIN mdl_course c ON l.course =c.id where l.time BETWEEN 1324729113 AND 1324729220
GROUP BY courseId
ORDER BY hits DESC

But it will not display all the couses as the previous query return.

Any solution

Average of ratings: -
In reply to Alejandro Cemylto

Re: SQL query

by Eddie None -

Can anyone tell me why if I take this line out of a report I get errors? I don't want any date of any kind in this report

   DATE_FORMAT(DATE_ADD('1970-01-01', INTERVAL gi.timemodified SECOND),'%c.%d.%Y') AS Date_Modified

Thanks in advance

Eddie

Average of ratings: -
In reply to Eddie None

Re: SQL query

by Emma Richardson -
Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Plugin developers

Would have to see the whole query to figure that out.  I suspect that this references something else in the query or that you have left a part of the line in there..

Average of ratings: -
In reply to Emma Richardson

Re: SQL query

by Eddie None -

I have attached the complete query - a bit of a mess I know - but I really appreciate any help. I have spent the whole day trying to use GROUP BY user id without any luck if you have any suggestions. I am s complete novice when it comes to sql !!!

SELECT u.firstname AS 'First' , u.lastname AS 'Last', CONCAT(u.firstname, ' ',u.lastname) AS 'Full Name',
    CASE
      WHEN gi.itemtype = 'Course'
      THEN c.Fullname
     ELSE gi.itemname
    END AS 'Course',
   
    CASE
      WHEN gg.finalgrade > 0
       THEN ROUND(gg.finalgrade)
      ELSE 'INC'
    END AS 'Grade',

    CASE
      WHEN ROUND(gg.finalgrade) >= 98
      THEN 'A+'
       WHEN ROUND(gg.finalgrade) >= 93
       THEN 'A'
        WHEN ROUND(gg.finalgrade) >= 90
        THEN 'A-'
         WHEN ROUND(gg.finalgrade) >= 87
         THEN 'B+'
          WHEN ROUND(gg.finalgrade) >= 83
          THEN 'B'
           WHEN ROUND(gg.finalgrade) >= 80
           THEN 'B-'
            WHEN ROUND(gg.finalgrade) >= 77
            THEN 'C+'
             WHEN ROUND(gg.finalgrade) >= 73
             THEN 'C'
              WHEN ROUND(gg.finalgrade) >= 70
              THEN 'C-'
               WHEN ROUND(gg.finalgrade) >= 67
               THEN 'D+'
                WHEN ROUND(gg.finalgrade) >= 63
                THEN 'D'
                 WHEN ROUND(gg.finalgrade) >=60
                 THEN 'D-'
                  WHEN ROUND(gg.finalgrade) >=0
                  THEN 'F' 
       ELSE 'INC'
      END AS 'Letter Grade',
    
CASE
      WHEN ROUND(gg.finalgrade) >= 98
      THEN '4.0'
       WHEN ROUND(gg.finalgrade) >= 93
       THEN '4.0'
        WHEN ROUND(gg.finalgrade) >= 90
        THEN '3.7'
         WHEN ROUND(gg.finalgrade) >= 87
         THEN '3.3'
          WHEN ROUND(gg.finalgrade) >= 83
          THEN '3.0'
           WHEN ROUND(gg.finalgrade) >= 80
           THEN '2.7'
            WHEN ROUND(gg.finalgrade) >= 77
            THEN '2.3'
             WHEN ROUND(gg.finalgrade) >= 73
             THEN '2.0'
              WHEN ROUND(gg.finalgrade) >= 70
              THEN '1.7'
               WHEN ROUND(gg.finalgrade) >= 67
               THEN '1.3'
                WHEN ROUND(gg.finalgrade) >= 63
                THEN '1.0'
                 WHEN ROUND(gg.finalgrade) >=60
                 THEN '1.0'
                  WHEN ROUND(gg.finalgrade) >=0
                  THEN '0' 
       ELSE '0'
      END AS 'GPA Value',
    
     gg.feedback AS 'Comments',
     u.department AS 'Year Group',

      DATE_FORMAT(DATE_ADD('1970-01-01', INTERVAL gi.timemodified SECOND),'%c.%d.%Y') AS Date_Modified
    
       
    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
    JOIN prefix_course_categories AS cc ON cc.id = c.category
    WHERE  gi.courseid = c.id AND gi.itemtype = 'course' AND cc.name != 'Miscellaneous' AND u.department != 'Staff' AND u.deleted = '0' AND u.suspended = '0'
ORDER BY u.department, u.firstname, u.lastname

Average of ratings: -
In reply to Eddie None

Re: SQL query

by Lorelei Glenn -

Would anyone know why I can only use the above format to show the dates? I'm getting dates with periods, but if I change the format to have blanks (where period is) or slash (/) I get nothing. I'm running MySQL on a local install for dev/testing purposes. I need to have the date formatted with slashes, like this 01/23/2014.

 

This is what my SQL is using right now:

DATE_FORMAT(DATE_ADD('1970-01-01', INTERVAL st.timemodified SECOND),'%c.%d.%Y')

Average of ratings: -