Blackboard SQL query (bb_bb60 schema) for SQL SERVER (t-sql) -- retrieve enrollments in courses where @USER_ID (username) is an instructor

get_student_enrollment_from_user_courses.sql

use bb_bb60

declare @USER_ID varchar(8);
set @USER_ID= 'username'

SELECT 
	distinct cm.COURSE_NAME
    , COUNT(cu.USERS_PK1) OVER (PARTITION BY cu.CRSMAIN_PK1) AS Num_students
  FROM COURSE_USERS cu
  join COURSE_MAIN cm on cu.CRSMAIN_PK1 = cm.PK1
  where 
	cu.CRSMAIN_PK1 in (select CRSMAIN_PK1 from COURSE_USERS cu where cu.USERS_PK1 = 
		(select PK1 from USERS u where u.USER_ID = @USER_ID) 
		and cu.ROLE = 'P')
    and cu.ROLE = 'S'

 

Note - you may need to change the number of characters for the declare @USER_ID varchar(8) statement depending on your installation's username implementation