I am looking to see if anyone has a postgres query that will return courses for a given term that are unavailable/available.
The below query will list all courses and their terms along with the course availability.
select cm.course_id, t."name", cm.available_ind from course_main cm
left join course_term ct on ct.crsmain_pk1 = cm.pk1
left join term t on t.pk1 = ct.term_pk1
--where t.name = ''
If you want to find courses for a specific term you can uncomment the last line and add the name of the term(in single quotes) and that should give you the courses by term.
Thank you very much Rashi.
I was able to create one and this will also be helpful.
I really appreciate your assistance. Thank you for replying.
Reminder that this community supports data inquiries like this too.
Bb Learn Data Reporting User Group
Note that I use this case statement to summarize available, unavailable and disabled into a single field, "status"
WHEN course_main.row_status ='2' Then 'Disabled'
WHEN course_main.available_ind = 'Y' THEN 'Available'
WHEN course_main.available_ind = 'N' THEN 'Unavailable'
ELSE 'Other' END AS "STATUS"
Thanks Jeff, I see how that could be very helpful.
I have found that in my query, which I need to adjust, it is picking up all the child courses that are unavailable and skewing my numbers. I need to try figure out a way to get all unavailable courses for a term but exclude the child courses. I may have to contact Tim Grady for that one.
Course merge relationships are stored in COURSE_COURSE table.
To get the course_id of the parent, you have to use the little trick of joining the course_main table twice (see last line)
cm.course_id as "COURSE_ID",
cm.course_name as "COURSE_NAME",
--compressed to Available, Unavailable, AND Disabled
WHEN cm.row_status ='2' Then 'Disabled'
WHEN cm.available_ind = 'Y' THEN 'Available'
WHEN cm.available_ind = 'N' THEN 'Unavailable'
ELSE 'Other' END AS "COURSE_STATUS",
-- Merged Courses?
coalesce(merge.children,0) AS "NUM_CHILD_CRS",
cm2.course_id AS "PARENT_COURSE_ID"
-- main tables
-- join tables for determining course merge status
-- count child courses for parent
LEFT JOIN (
SUM (CASE WHEN row_status = 0 THEN 1 ELSE 0 END) children,
GROUP BY crsmain_parent_pk1
) merge ON cm.pk1 = merge.crsmain_parent_pk1
-- get parent course ID if there is one
LEFT JOIN course_course cc on cm.pk1 = cc.crsmain_pk1
LEFT JOIN course_main cm2 ON cc.crsmain_parent_pk1 = cm2.pk1
That's awesome. Thank you Jeff.
does this query works also for ORACLE db?
from my mobile
Retrieving data ...