js0054664

DDA Enrollment Query Displays Child Enrollments in Parent Courses.

Discussion created by js0054664 on Jul 8, 2019

Hi everyone! I have a DDA query (shown below) I’ve been using to capture enrollments and last access data. We’ve found an issue with how this displays enrollments from crosslisted/merged courses. Enrollments in the child courses also appear in the parent courses. Since we have so many merged courses this query becomes less useful.

 

select
    cm.course_id AS "Course ID",
    cm.batch_uid AS "External Course Key",
    cm.course_name AS "Course Name",
    uu.USER_ID as "Username",
    CONCAT (uu.lastname, ', ', uu.firstname) as "Name",
    uu.student_id AS "Student ID",
    uu.batch_uid AS "External Person Key",
    uu.email AS "User Email",
    uu.last_login_date AS "Last Blackboard Access Date",
    cu.last_access_date AS "Last Course Access",
    cu.role AS "Role",
    CASE
            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",
    CASE
            WHEN uu.row_status = '2'    THEN 'Disabled'
            WHEN uu.available_ind = 'Y' THEN 'Available'
            WHEN uu.available_ind = 'N' THEN 'Unavailable'
            ELSE 'Other'
        END
    AS "User Status",
    CASE
            WHEN cu.row_status = '2'    THEN 'Disabled'
            WHEN cu.available_ind = 'Y' THEN 'Available'
            WHEN cu.available_ind = 'N' THEN 'Unavailable'
            ELSE 'Other'
        END
    AS "Enrollment Status"
    from COURSE_USERS cu
        join USERS uu on cu.USERS_PK1 = uu.PK1
        join COURSE_MAIN cm on cu.CRSMAIN_PK1 = cm.PK1
    where cm.COURSE_ID like '%2019%' -- Insert term portion of the course ID
    order by cm.COURSE_ID
;

 

I see the crosslisting data is in the course_course table, but I’m not sure how to use that data to remove the incorrect results from this query’s resultset. Is there any way to update this query to prevent the users enrolled in the child course from being displayed in the parent course?Any help is appreciated!

Outcomes