Lists all courses that are still in Original (Classic) format and indicates when a student last used them. Each row includes the academic term, the Data Source Key, status and availability, start and end dates, a student’s last access, the number of student enrollments, and the course size in MB.

It is the starting point for planning a move to Ultra or a cleanup: courses that no student has opened for some time are candidates for archiving, while those still in use are the first ones to convert. Only student access counts, so a course used only by its instructors appears without a date; courses without an academic term are excluded. Uncomment the dsc.batch_uid filter to work with a single Data Source Key.

SELECT cm.course_id                       AS "Course ID",
       t.name                             AS "Term Name",
       t.sourcedid_id                     AS "Term ID",
       dsc.batch_uid                      AS "Course DSK",

       CASE
           WHEN cm.row_status = 0 THEN 'ENABLED'
           WHEN cm.row_status = 2 THEN 'DISABLED'
           ELSE COALESCE(cm.row_status::text, '')
           END                            AS "Row Status",

       TO_CHAR(cm.start_date, 'YYYYMMDD') AS "Start Date",
       TO_CHAR(cm.end_date, 'YYYYMMDD')   AS "End Date",

       CASE
           WHEN cm.available_ind = 'Y' THEN 'Yes'
           WHEN cm.available_ind = 'N' THEN 'No'
           ELSE cm.available_ind
           END                            AS "Available Ind",

       CASE
           WHEN cm.course_view_option = 'I' THEN 'Instructor Choice'
           WHEN cm.course_view_option = 'U' THEN 'Ultra'
           WHEN cm.course_view_option = 'C' THEN 'Original'
           ELSE cm.course_view_option
           END                            AS "Course View Option",

       CASE
           WHEN cm.ultra_status = 'N' THEN 'Undecided'
           WHEN cm.ultra_status = 'C' THEN 'Classic'
           WHEN cm.ultra_status = 'U' THEN 'Ultra'
           WHEN cm.ultra_status = 'P' THEN 'Ultra Preview'
           ELSE cm.ultra_status
           END                            AS "Ultra Status",

       MAX(cu.last_access_date)
       FILTER (WHERE cu.role = 'S')       AS "Last Access Date",

       COUNT(DISTINCT cu.pk1)
       FILTER (WHERE cu.role = 'S')       AS "Student Enrollment Count",

       ROUND(
               COALESCE(cs.size_total, 0)::numeric / 1048576,
               2
       )                                  AS "Total Course Size (MB)"

FROM course_main cm

         LEFT JOIN course_users cu
                   ON cu.crsmain_pk1 = cm.pk1

         INNER JOIN data_source dsc
                    ON dsc.pk1 = cm.data_src_pk1

         LEFT JOIN course_size cs
                   ON cs.crsmain_pk1 = cm.pk1

         INNER JOIN course_term ct
                    ON ct.crsmain_pk1 = cm.pk1

         INNER JOIN term t
                    ON t.pk1 = ct.term_pk1

WHERE cm.ultra_status = 'C'
-- AND dsc.batch_uid = '1253'

GROUP BY cm.course_id,
         t.name,
         t.sourcedid_id,
         dsc.batch_uid,
         cm.row_status,
         cm.start_date,
         cm.end_date,
         cm.available_ind,
         cm.course_view_option,
         cm.ultra_status,
         cs.size_total
ORDER BY cm.course_id;

Output Columns