Banner Course Query

sections-with-descriptions.sql

SELECT  ssbsect.ssbsect_term_code AS term_code,
        scbcrse.scbcrse_subj_code AS subject_code,
        scbcrse.scbcrse_crse_numb AS course_number,
        ssbsect_seq_numb AS section_number,
        scbcrse.scbcrse_title AS course_title,
        COALESCE (ssbsect_crse_title, scbcrse_title) as section_title,
        scbcrse.scbcrse_credit_hr_low AS credit_hours_min,
        scbcrse.scbcrse_credit_hr_high AS credit_hours_max,
        scbcrse.scbcrse_lec_hr_low AS lecture_hours_min,
        scbcrse.scbcrse_lec_hr_high AS lecture_hours_max,
        scbcrse.scbcrse_lab_hr_low AS lab_hours_min,
        scbcrse.scbcrse_lab_hr_high AS lab_hours_max,
        scbdesc.scbdesc_text_narrative AS course_description
FROM ssbsect
     JOIN scbcrse
         ON     scbcrse_subj_code = ssbsect_subj_code
            AND scbcrse_crse_numb = ssbsect_crse_numb
            AND scbcrse_eff_term =
                (SELECT MAX (scbcrse_eff_term)
                 FROM scbcrse
                 WHERE     scbcrse_subj_code = ssbsect_subj_code
                       AND scbcrse_crse_numb = ssbsect_crse_numb
                       AND scbcrse_eff_term <= ssbsect_term_code)
     JOIN scbdesc
         ON     scbdesc_subj_code = ssbsect_subj_code
            AND scbdesc_crse_numb = ssbsect_crse_numb
            AND scbdesc_term_code_eff =
                (SELECT MAX (scbdesc_term_code_eff)
                 FROM scbdesc
                 WHERE     scbdesc_subj_code = ssbsect_subj_code
                       AND scbdesc_crse_numb = ssbsect_crse_numb
                       AND scbdesc_term_code_eff <= ssbsect_term_code)                   
     JOIN STVTERM
         ON STVTERM.STVTERM_CODE = ssbsect.ssbsect_term_code
    WHERE STVTERM.STVTERM_END_DATE >= SYSDATE AND STVTERM.STVTERM_START_DATE <= SYSDATE;