student_schedule_pull

SELECT
--   This next substring field may need to be edited depending on your timeblock names. As of now, it is pulling the proper label for a timeblock setup as "01.M3A"
  students.local_student_id || ' ' || substring(timeblocks.timeblock_name,4,3) AS tb_lookup,
  students.local_student_id || ' ' || sections.section_id as sect_lookup,
  CAST (students.local_student_id AS int)                 AS local_id,
  CAST (students.student_id AS int),
  students.last_name,
  students.first_name,
  (matviews.student_term_aff.grade_level_id - 1)  AS current_grade,
  houses.house_name,
  sections.section_id,
  timeblocks.timeblock_name,
--   This next field may need to be edited depending on your timeblock names. As of now, it is pulling the proper label for a timeblock setup as "01.M3A"
  CASE WHEN matviews.student_term_aff.grade_level_id < 10 THEN substring(timeblocks.timeblock_name,4,3) ELSE substring(timeblocks.timeblock_name,4,3) ||'H' END AS time,

    CASE WHEN courses.school_course_id = 'B100' THEN 'English 9' ELSE
      CASE WHEN courses.school_course_id = 'B200' THEN 'English 10' ELSE
        CASE WHEN courses.school_course_id = 'I501M' THEN 'Mentor PLT' ELSE
          CASE WHEN substring(courses.school_course_id,1,4)='I501' THEN 'PLT' ELSE
            CASE WHEN substring(courses.school_course_id,1,2) = 'S2' THEN 'Summit Reads' ELSE
              CASE WHEN courses.school_course_id = 'I1107' OR courses.school_course_id = 'I1108' THEN 'Intro to Comp Sci' ELSE
                CASE WHEN courses.school_course_id = 'I023' OR courses.school_course_id = 'I024' THEN 'Intro to Design' ELSE
                  CASE WHEN courses.school_course_id = 'I007' OR courses.school_course_id = 'I008' THEN 'Drama' ELSE
                    CASE WHEN substring(courses.school_course_id,1,5) = 'B300' THEN 'AP English Language' ELSE
                        CASE WHEN substring(courses.school_course_id,1,5) = 'B400' THEN 'AP English Literature' ELSE
                            CASE WHEN substring(courses.school_course_id,1,2) = 'S3' THEN 'Summit Solves' ELSE
                              CASE WHEN substring(courses.school_course_id,1,2) = 'LC' THEN 'Learning Center' ELSE
                    courses.short_name
                      END
                    END
                  END
                END
              END
            END
          END
         END
        END
      END
    END
  END,
  to_char(timeblock_times.start_time, 'HH12:MI AM'),
  to_char(timeblock_times.end_time, 'HH12:MI AM'),
  users.last_name,
  rooms.room_number
FROM
  section_student_aff
LEFT JOIN students ON section_student_aff.student_id = students.student_id
LEFT JOIN matviews.student_term_aff on matviews.student_term_aff.student_id = section_student_aff.student_id
LEFT JOIN terms on terms.term_id = matviews.student_term_aff.term_id
LEFT JOIN sessions on sessions.session_id = terms.session_id
LEFT JOIN section_timeblock_aff on section_timeblock_aff.section_id = section_student_aff.section_id
LEFT JOIN timeblocks on timeblocks.timeblock_id = section_timeblock_aff.timeblock_id
LEFT JOIN sections on section_student_aff.section_id = sections.section_id
LEFT JOIN courses ON section_student_aff.course_id = courses.course_id
LEFT JOIN timeblock_times ON timeblocks.timeblock_id = timeblock_times.timeblock_id
LEFT JOIN day_types on timeblock_times.day_type_id = day_types.day_type_id
    LEFT JOIN section_teacher_aff on sections.section_id = section_teacher_aff.section_id
LEFT JOIN users on users.user_id = section_teacher_aff.user_id
LEFT JOIN rooms ON rooms.room_id = sections.room_id
LEFT JOIN student_house_aff ON students.student_id = student_house_aff.student_id
LEFT JOIN houses ON houses.house_id = student_house_aff.house_id
LEFT JOIN section_term_aff on sections.section_id = section_term_aff.section_id
WHERE
  matviews.student_term_aff.entry_date > '2017-08-01' AND
  section_term_aff.term_id >= 261  AND
  section_teacher_aff.primary_teacher IS TRUE AND
  (section_student_aff.leave_date >  current_date OR section_student_aff.leave_date IS NULL)
-- Edit the session IDs below to match the session id for your site this year.
  sessions.session_id = 216 AND
  houses.session_id = 216 AND
-- Edit these day type codes to match the standard week day types: 'Monday Schedules' thru 'Friday Schedules'
  day_types.character_code IN ('M','T' ,'D','S','F')

GROUP BY
  students.local_student_id,
  students.student_id,
  students.last_name,
  students.first_name,
  matviews.student_term_aff.grade_level_id,
  timeblocks.timeblock_name,
  sections.section_id,
  courses.school_course_id,
  courses.short_name,
  timeblock_times.start_time,
  timeblock_times.end_time,
  users.last_name,
  rooms.room_number,
  houses.house_name
ORDER BY
  matviews.student_term_aff.grade_level_id,
  students.local_student_id,
  timeblock_name