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'?
For simple quick analysis, I use course_main DTMODIFIED