Initially the query CU_BAR_CM_00026 was created to count the enrollment for the students who has taken CSTM 120 class previously. However, the query could not pick up some of students who has taken FSPM 121 class as a first time and received 'R, W,WU, WN, FIN or WUINC'. Therefore, those students need to retake the class.
...
- UGRAD students in (1159) or current semester
- Currently enrolled for CSTM 120 / FSPM 121 (summer only)
- Who have not taken either or both the courses previously
- Received the grade as R, W, WU, WN, FIN or INC
Final Report Delivery with requirements:
...
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 | SUPPORT CLASS_TBL_SE_VW ABOVE and counting STRM |
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 ), '091787' (only if FSPM exist)
Sub query: EXIST
CA.CRSE_GRADE_OFF - Official Grade in list ('R','W','WU') (the result is filtering only 'R','W','WU' grades)
Sub query: NOT EXIST
E.CRSE_GRADE_OFF = 'P' (eliminating students who have passed already)
Query Output Image:
...
| ID | First Name | Middle | Last | Course List | Count |
|---|---|---|---|---|---|
| 16093648 | Kamal | M | Bashiru | 1119 CSTM 120 02 WU, 1112 CSTM 120 01 WU | 2 |
SQL Summery:
SELECT A.EMPLID, D.FIRST_NAME, D.MIDDLE_NAME, D.LAST_NAME, listagg( A.STRM || ' ' || A.SUBJECT || ' ' || A.CATALOG_NBR || ' ' || A.CLASS_SECTION || ' ' || A.CRSE_GRADE_OFF, ', ') within group (order by A.STRM desc), COUNT( A.CRSE_ID) FROM PS_CLASS_TBL_SE_VW A, PS_STDNT_CAR_TERM B, PS_CU_BIO_VW D WHERE ( A.CRSE_ID IN ('090828','091787') AND A.STRM <= :1 AND A.EMPLID = B.EMPLID AND A.ACAD_CAREER = B.ACAD_CAREER AND A.INSTITUTION = B.INSTITUTION AND B.STRM = :1 AND EXISTS (SELECT C.EMPLID FROM PS_CLASS_TBL_SE_VW C WHERE ( C.STRM = :1 AND C.CRSE_ID IN ('090828','091787') AND A.EMPLID = C.EMPLID AND A.CRSE_GRADE_OFF IN ('R','W','WU','WN') )) AND A.EMPLID = D.EMPLID AND A.INSTITUTION = :2 AND NOT EXISTS (SELECT E.EMPLID FROM PS_CLASS_TBL_SE_VW E WHERE ( A.CRSE_ID IN ('090828','091787') AND A.EMPLID = E.EMPLID AND E.CRSE_GRADE_OFF = 'P' AND E.STRM = :1 )) ) GROUP BY A.EMPLID, D.FIRST_NAME, D.MIDDLE_NAME, D.LAST_NAME ORDER BY 4, 2, 3 | |||||||||||||||||||||||
Other Desire Modification:
...