AnsweredAssumed Answered

Determine Empty Courses

Question asked by bishop.thomas on Apr 2, 2019
Latest reply on Apr 18, 2019 by bl31720

I need to determine if certain courses are devoid of content at the start of term. Currently I use the following query which checks for zero course size and counts how many Grade Center columns there are. Blank courses only have two so I must further limit the results to only those. Is there a better way of doing this?:


SELECT cm.course_id, cm.course_name, Count(gm.title) AS gc_column_title_count, cs.size_total

FROM public.course_main cm

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

LEFT JOIN public.course_contents cc ON cc.crsmain_pk1 = cm.pk1

LEFT JOIN public.gradebook_main gm ON gm.crsmain_pk1 = cm.pk1

WHERE cm.course_id LIKE '%.201840'

AND cm.course_name LIKE '%-OL%'

AND cm.course_name NOT LIKE 'SP19 X%'

AND cm.available_ind = 'Y'

AND cs.size_total = 0

GROUP BY cm.course_id, cm.course_name, cs.size_total

ORDER BY gc_column_title_count;


/* course_id will contain the term ID */

/* course_name will include "-OL" for fully online offerings */

/* filter out course numbers starting with 'X' as those are just workshops */

/* only courses still available (not terminated) */

/* course_size totals can be zero and still not devoid of content */


Would this be the definitive way of finding empty courses?

Is there a better way of limiting results to less than 3 for the aggregate 'gc_column_title_count'?