Export Student Schedules

Greetings!

I am in the process of inputting student timetables and am looking for a way to export that information into excel by student. Using the query builder module (Course/Class Selections in Current Year, under the Timetable category), I can get student names, courses, and classes. What I would like, however, is full course and class names (rather than abbreviations) along with class meeting times. Can someone help me build that query or point me in the direction of a resource to do so?

Thanks so much!

Comments

  • While messing around, I figured out how to get full course and class names, so that part is now solved.

    Still need to know how to get class times attached to my query.
    admin
  • And I would also like to attach room numbers to each class! I'm just asking for the moon here.
  • Hello, we don't mind people asking for the moon! Share your SQL, as it stands now, with us, and we'll see if we can offer some solutions.

    One source of inspiration is to take the SQL that creates the listing at Learn > Timetable > View Master Timetable and edit it to include students and order by individual.

    Thanks : ) Ross.
  • I'm just using this query for now, Ross, but it doesn't give me class times or room numbers.

    SELECT preferredName, surname, gibbonCourse.name AS course, gibbonCourseClass.name AS class FROM gibbonPerson JOIN gibbonCourseClassPerson ON (gibbonCourseClassPerson.gibbonPersonID=gibbonPerson.gibbonPersonID) JOIN gibbonCourseClass ON (gibbonCourseClassPerson.gibbonCourseClassID=gibbonCourseClass.gibbonCourseClassID) JOIN gibbonCourse ON (gibbonCourseClass.gibbonCourseID=gibbonCourse.gibbonCourseID) WHERE status='Full' AND role='Student' AND gibbonCourse.gibbonSchoolYearID=(SELECT gibbonSchoolYearID FROM gibbonSchoolYear WHERE status='Current') ORDER BY course, class, surname, preferredName ;

    I went so far as to look through the actual tables in using mysql, but I couldn't find which ones in which the data I needed was stored.

    I found "View Master Timetable", but how do I take the sql from that page? I'm afraid I'm self taught at most of this, so I need a little hand holding to make it work. I would also need help with the syntax necessary to line up this sql with including students.

    Thanks again!
  • Hello,

    That's a great start, and just a few more joins to various gibbonTT tables needed. Check out the query below, and let me know if that works for you:

    SELECT
    preferredName,
    surname,
    gibbonCourse.name AS course,
    gibbonCourseClass.name AS class,
    gibbonTTDay.name AS day,
    gibbonTTColumnRow.name AS period,
    substr(gibbonTTColumnRow.timeStart, 1, 5) AS timeStart,
    substr(gibbonTTColumnRow.timeEnd, 1, 5) AS timeEnd,
    gibbonSpace.name AS facility
    FROM
    gibbonPerson
    JOIN gibbonCourseClassPerson ON (gibbonCourseClassPerson.gibbonPersonID=gibbonPerson.gibbonPersonID)
    JOIN gibbonCourseClass ON (gibbonCourseClassPerson.gibbonCourseClassID=gibbonCourseClass.gibbonCourseClassID)
    JOIN gibbonCourse ON (gibbonCourseClass.gibbonCourseID=gibbonCourse.gibbonCourseID)
    JOIN gibbonTTDayRowClass ON (gibbonTTDayRowClass.gibbonCourseClassID=gibbonCourseClass.gibbonCourseClassID)
    JOIN gibbonTTDay ON (gibbonTTDayRowClass.gibbonTTDayID=gibbonTTDay.gibbonTTDayID)
    JOIN gibbonTTColumnRow ON (gibbonTTDayRowClass.gibbonTTColumnRowID=gibbonTTColumnRow.gibbonTTColumnRowID)
    JOIN gibbonTTColumn ON (gibbonTTColumnRow.gibbonTTColumnID=gibbonTTColumn.gibbonTTColumnID)
    JOIN gibbonSpace ON (gibbonTTDayRowClass.gibbonSpaceID=gibbonSpace.gibbonSpaceID)
    WHERE
    status='Full'
    AND role='Student'
    AND gibbonCourse.gibbonSchoolYearID=(SELECT gibbonSchoolYearID FROM gibbonSchoolYear WHERE status='Current')
    ORDER BY course, class, surname, preferredName ;


    Hope it helps!

    I'll also add this to Query Builder when I get a moment.

    Thanks,

    Ross
  • That works like a dream--thanks, Ross!
    admin
Sign In or Register to comment.