*SQL Query of Bb Vista/CE database

Kevin Donahue of Villanova University asked this question on the "WebCT" listserve a few days back. "I would like to generate a report that shows all the active sections /and/ unique users within a given semester." Brandon Rich, here at Notre Dame, designed such an animal for us. We call it the Super User Query because he went ahead and used the comment feature to include optional parameters. When the ‘–‘ precede a statement, that statement is considered a comment and ignored by the program as well as color-coded as a comment only. This one selects unique Instructors from sections which are active (as determined by the Restrict flag).

Hopefully it’s self-documented enough to be useful to others:

—–SUPER USAGE QUERY!!!
–select c.name as college, count(unique(p.ifsid)) as faculty_count — uncomment to count by college
–select a.organization_name as dept, count(unique(p.ifsid)) as faculty_count — uncomment to count by category (dept)
select unique(p.ifsid), c.name as dept, a.organization_name, p.name_n_family || ‘, ‘ || p.name_n_given as full_name — uncomment to list faculty by dept
from webct.rpt_learning_context a,
webct.rpt_learning_context b, webct.rpt_learning_context c,
webct.rpt_member m, person p
where b.learning_context_id = a.parent_learning_context_id
and c.learning_context_id = b.parent_learning_context_id
and m.learning_context_id = a.learning_context_id
and p.id = m.person_id
— and p.id not in — include to check designers only
— (
— select person_id from webct.rpt_member
— where role = ‘SINS’
— )
and ( m.role = ‘SINS’ ) — get instructors
–and ( m.role = ‘SDES’ )
and a.type = ‘SECTIONCTX’
and b.type = ‘COURSECTX’
and c.type = ‘CAMPUSCTX’
— Put your sourceids here or delete:
and c.learning_context_id != 32244 — way to exclude learning contexts by sourceid.id
and c.learning_context_id != 52292 — No College Designated
and a.source_id like ‘%&term_code%’ — When run will ask for the term
–and ( a.source_id like ‘%200520%’ or a.source_id like ‘%200510%’ or a.name like ‘SP06%’ or a.name like ‘FA05%’)
and a.learning_context_id in
(
select r.learning_context_id from webct.roster r
where r.startdate_time = r.enddate_time and r.start_restrict = 0 and r.end_restrict = 0
)
and p.name_n_given != ‘WebCT’
— these are exclusive. Uncomment only one:
–group by c.name — uncomment to count by college
–order by count(unique(p.ifsid)) DESC — uncomment to count by college
–group by a.organization_name — uncomment to count by category (dept)
–order by count(unique(p.ifsid)) DESC — uncomment to count by category (dept)
order by c.name, a.organization_name, full_name — uncomment to list faculty by dept

——————————————-
*Chris Geiger, Mgr. Blackboard Client Support, granted permission for public display of helpful sql queries. Be advised this sql snippet above queries reports which are a part of the PowerSight Kit.

Advertisements

2 responses to “*SQL Query of Bb Vista/CE database

  1. Bikash Chudal

    Hi Laura-
    This Query uses a table named ‘person’. My test system doesnot seems to have a table with that name. Is it something custom created in your db or is it refering to ‘webct.person’ table?
    Thank you,
    Bikash Chudal

  2. Bikash,
    That is a good catch. The line which says “webct.rpt_member m, person p” should be fully qualified with the schema owner (webct) as the others are, especially as report queries should be run under accounts which do not have write access.
    Thank you for bringing this to my attention.
    -LG

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s