User enrollment with data_source batch_uid for user, course, and enrollment

SELECT cu.pk1,
u.user_id,
cm.course_id,
dsc.batch_uid as Cds,
dsu.batch_uid as Uds,
dscu.batch_uid as CUds,
cu.available_ind,
cu.row_status,
cu.dtmodified
FROM course_users cu
INNER JOIN users u on cu.users_pk1 = u.pk1
INNER JOIN course_main cm on cu.crsmain_pk1 = cm.pk1
INNER JOIN data_source dscu on cu.data_src_pk1 = dscu.pk1
INNER JOIN data_source dsu on u.data_src_pk1 = dsu.pk1
INNER JOIN data_source dsc on cm.data_src_pk1 = dsc.pk1
WHERE u.user_id = 'auser' and cm.course_id LIKE '%S12011'

Select all student enrollments from FA2010 (course_users)

SELECT u.user_id, course_main.course_id, cu.available_ind,
cu.row_status, cu.data_src_pk1 as ENR_DSRC,
u.data_src_pk1 as USER_DSRC, cu.dtmodified
FROM course_users cu INNER JOIN course_main cm on cu.crsmain_pk1 = cm.pk1
INNER JOIN u ON cu.users_pk1 = u.pk1
WHERE course_main.course_id LIKE '%FA2010%'AND
cu.data_src_pk1 < '20' AND u.user_id NOT LIKE 'stu_%' AND
u.user_id not like '%student%' AND u.user_id not like '%faculty%' AND
u.user_id <> 'bross' AND u.user_id NOT LIKE 'temp-olw%' AND
cu.role = 'S' and u.institution_roles_pk1 = '1'

Disable enrollments of all students for FA2010 (course_users)

update cu
SET row_status = '2'
FROM course_users cu
INNER JOIN course_main cm ON cu.crsmain_pk1 = cm.pk1
INNER JOIN users u ON cu.users_pk1 = u.pk1
WHERE cm.course_id LIKE '%FA2010%' AND
course_users.data_src_pk1 < '20' AND u.user_id not like 'stu_%' AND
u.user_id not like '%student%' AND u.user_id not like '%faculty%' AND
u.user_id <> 'bross' AND
u.user_id NOT LIKE 'temp-olw%' AND cu.role = 'S' AND
u.institution_roles_pk1 = '1'

Turn off availability of student enrollments in a course

UPDATE cu
set available_ind = 'N'
FROM course_users cu
INNER JOIN course_main cm ON cu.crsmain_pk1 = cm.pk1
WHERE cm.course_id = 'Adjunct_Orientation' AND cu.role = 'S'

User activity raw (activity_accumulator)

USE BBLEARN
SELECT u.user_id, aa.event_type, cm.course_id,
aa.internal_handle, aa.data, aa.timestamp, aa.messages, aa.session_id,
datediff(dy, timestamp, CURRENT_TIMESTAMP) as ddiff
FROM activity_accumulator aa
INNER JOIN course_main cm ON aa.course_pk1 = cm.pk1
INNER JOIN users u ON aa.user_pk1 = u.pk1
WHERE u.user_id = 'someuser'
AND datediff(dy, timestamp, CURRENT_TIMESTAMP) < 10
AND course_id <> ''

List courses with course_id matching pattern with last - delmited token (ie the term in WBCT-1003-030IN-FA2010)

SELECT course_main.course_id, RIGHT(course_id,PATINDEX('%-%',REVERSE(course_id))-1) AS Term
FROM course_main
WHERE course_main.course_id like 'WBCT-%'

List courses created on a certain day, sorted by row_status, availability and course_id

DECLARE @Qdate  SMALLDATETIME
SET @Qdate = '12/02/2010'
SELECT cm.course_id, cm.dtcreated,
cm.dtmodified, cm.available_ind, cm.row_status
FROM course_main cm
INNER JOIN course_users cu ON cm.pk1 = cu.crsmain_pk1
INNER JOIN users ON cu.users_pk1 = users.pk1
WHERE cm.row_status = '0' AND cm.course_id like '%SP2011' AND
cm.dtcreated BETWEEN @Qdate AND DATEADD(day,1,@Qdate)
ORDER BY cm.row_status DESC, cm.available_ind ASC, cm.course_id ASC

Fix for users not showing up in messaging user dropdown

UPDATE course_users
SET receive_email_ind = 'Y', roster_ind = 'Y'
WHERE (roster_ind = 'N' OR receive_email_ind = 'N') AND available_ind = 'Y';

Forums with ENFORCE_MODERATION enabled

SELECT fr.pk1,
u.email,
cm.course_id,
fm.name,
fr.dtcreated,
fr.dtmodified
FROM forum_registry fr
INNER JOIN forum_main fm ON fr.forummain_pk1 = fm.pk1
INNER JOIN conference_main confm ON fm.confmain_pk1 = confm.pk1 
INNER JOIN course_main cm ONconfm.conference_owner_pk1 = cm.pk1
INNER JOIN (SELECT * from course_users WHERE role = 'P') AS cu ON cm.pk1 = cu.crsmain_pk1
INNER JOIN users u ON cu.users_pk1 = u.pk1
WHERE registry_key LIKE 'ENFORCE%' AND registry_value = 'Y'