Blackboard Learn SQL Queries
Home » Blackboard » Blackboard Learn » Blackboard Learn SQL Queries » SQL: List All Courses Accessed by a System Role in the Last 7 Days

SQL: List All Courses Accessed by a System Role in the Last 7 Days

Summary

This query lists all courses and organizations that have been accessed by a user with a specific system role in the last 7 days.

Data Source: Blackboard DDA on SaaS

Code

SELECT DISTINCT u.lastname, u.firstname, cm.course_name, 
 CASE
    WHEN cm.service_level = 'C' THEN 'Organization'
    WHEN cm.service_level = 'F' THEN 'Course'
    ELSE 'Other'
 END as item_type
FROM PUBLIC.activity_accumulator aa
LEFT JOIN PUBLIC.users u
ON aa.user_pk1 = u.pk1
LEFT JOIN PUBLIC.course_main cm
ON aa.course_pk1 = cm.pk1 
WHERE u.system_role = 'HELPDESK_SUPPORT' --Enter Your System Role Here
AND u.row_status = '0' --Filters Only Active Users
AND aa.event_type = 'COURSE_ACCESS' --Filters Only Course Access
AND aa.timestamp < current_date - interval '7' day --Filters Last 7 Days
ORDER BY u.lastname, u.firstname

Eric Silva

Eric is an Instructional Technologist overseeing Online Student Support Initiatives at The University of Texas Rio Grande Valley and a graduate from Boise State University. Learn more about me.

Add comment

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.