Queries whose name begins with jchem. Primary contact is Tammy Candido candidtd@jmu.edu Section last updated 2/14/2012.
jchem001 (NTS): Used to compare grades in chem courses: 131, 132, 221, 222, 341, 342, 361 with SAT test scores. Allows a term range. Returns just the primary major for the given student.
jchem002 (NTS): Chem crses by acad data. Only difference from jchem001 is this version returns ALL majors and minors for the student.
jchem003 (TS): Unlike jchem001/2 which has hard coded chem courses, this version returns all courses whose subject is chem.
jchem004 (NTS): Math before chem grades. Returns admit type=FYR students for the given admit term who were enrolled in chem131 and the math courses they took prior to being enrolled in chem131.
jchem004a (NTS): Math after chem grades. Inverse of jchem004; returns the math courses the student took after chem131.
jchem005 (NTS): Best grade pts in two courses. Prompts for a term range and up to two courses. Returns true Freshmen who have taken either/both of the two courses and the best grade points they received in each course. Wanted the ability to compare the highest grade achieved @ JMU for any two given courses and the sat1 math score.
jchem005a (NTS): Wanted a version that contained the term and the grade, so could not return the highest grade.
jchem006 (NTS): Chemistry acad data. Has hard coded references to chemistry plans, similar results to jusds002 with fewer fields and sub-plan transcript description.
jchem007 (NTS): Primary difference from jchem006 is this version has a yes/no flag for whether the student has a sub-plan attached to the plan.
CIT (Center for Instructional Technology)
Queries whose name begins with jcit and jblue. Primary contact is Donna Davis davisdy@jmu.edu Queries whose name begins with jblue were developed for use by CIT staff to extract data from the SA system into the course evaluation system known as “Blue”. Section last updated 5/4/2018.
jcit01 (NTS) On line courses offered. Prompts for a term and returns course data for courses defined as being available online. In this context, online is defined as the course having an instruction mode of a value of WW or LI OR with a location of a value of ONLINE or OP ONLINE. Returns just the primary instructor.
jblue01 (TS): Student data for blue. Prompts for a term. Returns basic student biographical information if the student is enrolled in hours for the given term. Student must also be enrolled in one or more of the following courses as defined by CIT. The following criteria is present in all jblue queries.
A course whose subject is one of the following values:
OR the first two characters of the course section=OP (these are outreach and engagement courses)
OR the crse_id is one of these values: 009039,005505, 001889, 003085, 002621, 000334, 006996, 005382, 011578, 011579, 011580, 006416, 007548, 008514, 009708, 003791,001891
OR the course catalog number has the capital letter H present (indicating it is an honors course)
jblue02 (NTS): Instructor data for blue. Prompts for a term. Return instructor biographical information if they are teaching a non cancelled section of a course for the given term for courses defined in 2 a-d above. Instructors whose role is TA (teaching Assistant) are not returned.
jblue03 (NTS): Course data for blue. Prompts for a term, session (can be left blank to be ignored) and department (which is actually the academic org value for the given course and can be left blank to be ignored). Returns course specific data for all courses for the given term for courses defined in 2 a-d above. Of special note; this is a union query because the blue system needs to know if a given course has multiple instructors or not. The first selection returns only those courses with multiple instructors, the second selection only single instructor courses. A single Y/N field is used to distinguish the different selections. Special notes for the following two fields labeled:
Survey start date. Is calculated based upon several fields as described in the expression text which follows. Of special note is THIS field is NOT automatically used by the mymadison role based query which determines IF the hyperlink for access to the blue evaluation system appears. There is a separate role based query in pprd used for this logic which MUST match the following expression logic. case when substr(A.STRM,4,1) = '5' then TO_DATE(A.END_DT) – 5 when substr(A.STRM,4,1) <> '5' and A.SESSION_CODE = '1' then TO_DATE(A.END_DT) - 17 else TO_DATE(A.END_DT) - 8 end
Survey end date. Similar to above expect that this date is always/forever calculated as 1 day before the actual course end date, so the expression text is: TO_DATE(A.END_DT) -1
jblue04 (NTS): Enrollment data for blue. Prompts for a term. Basically, returns the intersection of the jblue01 and jblue03 queries. Returns just two fields. A concatenated field labeled courseid (crse_id, class_nbr, strm, and session_code) and the SA Emplid. The given student must be enrolled in a course defined in 2 a-d above.
jblue05 (NTS): Course instructor data for blue. Prompts for a term. Basically, returns the intersection of the jblue01 and jblue02 queries. Returns just two fields. A concatenated field labeled courseid (crse_id, class_nbr, strm, and session_code) and the SA Emplid of the instructor labeled InstructorID. The given instructor must be teaching a course defined in 2 a-d above. The query does not return instructors whose role is TA (Teaching Assistant).
jblue05a (NTS): Only difference in this version from jblue05 is that it returns18 fields related to the instructor and the course being taught by them vs. the two fields returned by jblue05.
jblue05a_onlineonly (NTS): Similar to jblue05, but only returns data if the course is taught online defined as the instruction mode=WW. The onlineonly suffixed jblue queries do NOT contain the criteria listed in 2 a-d above.
jblue06 (NTS): Course Administrator data. Prompts for a term. Returns 15 columns of data about courses for the given term which meet the criteria in 2 a-d above along with the respective course administrator. In this course, the course administrator is labeled as the “supervisor”. Also noteworthy is the fact this query does not return course administrator defined in the jsr_crse_admin record defined just for the blue system because of courses taught across multiple “departments”. The only key to the jsr_crse_admin record is subject, which then yields a single course administrator for all courses with the given subject. In some cases, there are multiple course administrators for a given subject and course, and even to the course section level. This is accounted for by entering the SA emplid at the course level which is what this query returns. The courses returned by this query msut meet the criteria defined in 2 a-d above. This query does not return courses whose class status is cancelled.
My Madison jblue queries. There are three queries in My Madison (aka pprd) which allow three different populations of students, instructors and course administrators access to the Blue evaluation system via a link in My Madison. These queries are all built over the psoprdefn record and return a single field, the oprid of the person allowed to access the blue application. Each query has logic designed by CIT to only allow access when a very specific window of time is open. This window of time is based upon the course end date, the term and the session code. It uses the system date for its calculations. These queries include the logic defined in 2 a-c above. The timelines used for when access opens and closes (aka the window) is defined in a spreadsheet attached to the issue and op ITI08671 defined by CIT.
Jblue06 (TS): Instructor data for portal. If the session is a value of 1 or 8W2, the instructor is allowed access to blue within 60 days of the course end date. If the course session is 8W1, the instructor is allowed access to blue within 30 days of the course end date. For all summer term courses, the logic states the instructor is allowed access the day the course starts.
Jblue07 (TS): Student data for portal.
For summer term courses, a student is allowed access to blue for a given course 5 days prior to the course end date until the actual end date.
For terms other than summer and when the session =1, the window opens 18 days prior to the course end date, and closes 1 days prior to the course end date.
For terms other than summer and when the session <> 1, the window opens 8 days prior to the course end date, and closes 1 days prior to the course end date.
Jblue08 (TS): Supervisor data for portal. The window for a course administrator opens the day they are assigned as the course administrator, and never closes.
