Background:
Warren B. Gordon Professor and Chair the Department of Mathematics wanted a report for students enrolled in CSTM 0120 course based on a calculation of prior enrollment history.
Patrick,
I need the output in Excel in a roster format, that is for each
student enrolled in CSTM 0120 in a particular semester, I need
to know the number of previous times that student took this same
course. There is no need for grades, since each previous time
they received a R grade.
I will run this query just before the start of each semester,
including summers. Students who previously took the course do
not get the course materials, as they already have online access
to it, we only distribute access to first time students. In
order to know this information, I need the above query.
Thanks
Warren
Criteria(s) are:
For the calculation of previous attempts the criteria to be used will be
previous enrollment in CSTM 0120 with grades of W, WU, or R.
Final Report Delivery with requirements:
The query for students enrolled in CSTM 0120 has been shared with Warren and Patrick. The query is working fine.
CU_BAR_CM_00026 Prompts are below:
- Institution: Bar01
- Term: 1156 or next term
Query Development:
Following table shows all necessary records that have been used to generate fields as requested by an user to create this report.
| Records | Fields from the Record |
|---|---|
| CLASS_TBL_SE_VW | EMPLID,SUBJECT,CATALOG_NBR, DESCR,CLASS_SECTION, SESSION_CODE,CLASS_NBR, ACAD_GROUP, GRADE_DT |
| CU_BIO_VW | FIRST_NAME, LAST_NAME, MIDDLE_NAME |
| STDNT_CAR_TERM | STRM |
| CLASS_TBL_SE_VW | SUPPORT CLASS_TBL_SE_VW ABOVE |
Expressions:
One 'Expressions' have been added to find 'Previous Attempts (R, W, WU)' to count the previous attempts of this particular class.
Prompt:
Institution, Term have been mandatory for an user to input to extract data
Criteria:
A.CRSE_ID - Course ID = '090828' (This is specifically looking for the course 'CSTM 120' attribute )
A.STDNT_ENRL_STATUS - Student Enrollment Status = 'E' (the report is based on enrolled students)
C.CRSE_GRADE_OFF - Official Grade in list ('R','W','WU') (the result is filtering only 'R','W','WU' grades)
Query Output Image:
SQL Summery:
SELECT A.EMPLID, B.FIRST_NAME, B.MIDDLE_NAME, B.LAST_NAME, A.SUBJECT, A.CATALOG_NBR, A.DESCR, A.CLASS_SECTION, A.SESSION_CODE, A.CLASS_NBR, A.ACAD_GROUP, count ( C.CLASS_NBR), TO_CHAR(A.GRADE_DT,'YYYY-MM-DD'),A.ACAD_CAREER,A.INSTITUTION,A.STRM FROM PS_CLASS_TBL_SE_VW A, PS_CU_BIO_VW B, PS_CLASS_TBL_SE_VW C WHERE A.INSTITUTION = :1 AND A.STRM = :2 AND A.CRSE_ID = '090828' AND B.EMPLID = A.EMPLID AND A.STDNT_ENRL_STATUS = 'E' AND A.CRSE_GRADE_OFF = ' ' AND A.EMPLID = C.EMPLID AND A.ACAD_CAREER = C.ACAD_CAREER AND A.INSTITUTION = C.INSTITUTION AND A.STRM >= C.STRM AND C.CRSE_GRADE_OFF IN ('R','W','WU') AND A.CRSE_ID = C.CRSE_ID GROUP BY A.EMPLID, B.FIRST_NAME, B.MIDDLE_NAME, B.LAST_NAME, A.SUBJECT, A.CATALOG_NBR, A.DESCR, A.CLASS_SECTION, A.SESSION_CODE, A.CLASS_NBR, A.ACAD_GROUP, TO_CHAR(A.GRADE_DT,'YYYY-MM-DD'),A.ACAD_CAREER,A.INSTITUTION,A.STRM ORDER BY 4, 2, 3 | |||||||||||||||||||||
Other Desire Modification: