Purpose: The query for cleaning up student groups for students who have passed a course.
Platform: CUNYFirst CS-Reporting
Query Name: CU_BAR_SR_CLNUP_SG_PASS_CLASS
Folder: BAR_REC_CLEAN_UP
Query Type: Private
Short Descr: Pass Class and in Student Group
Description: Creates a list of students active in a particular student group who have a grade that CUNYFirst considers to be passing at Baruch College in a particular course.
Prompts:
Inputs: Institution (Defaults to BAR01)
Career – This is a selectable dropdown linked to course career
Subject – Subject of Course Catalog – Catalog Number
Group Name – Student Group that the student is ACTIVE in
Output:
ID – Student EMPLID
Institution
Group Name – Student Group Name
Eff Date – Effective Date of Student Group Record
Status – Student Group Status (A = Active, I = Inactive)
Career – Course Career
Term – Term Code for Semester Student Passed Class
Session – Session Code for Semester Session Where Student Passed Class
Subject – Course Subject
Catalog – Course Catalog Number
Grade in – Grade Input
Descr – Long name for Term Student Passed Class
Cleanup Comment – Comment that includes Term and Class that Student Completed course.
SQL:
SELECT A.EMPLID, A.INSTITUTION, A.STDNT_GROUP, TO_CHAR(A.EFFDT,'YYYY-MM-DD'), A.EFF_STATUS, B.ACAD_CAREER, B.STRM, B.SESSION_CODE, B.SUBJECT, B.CATALOG_NBR, B.CRSE_GRADE_INPUT, D.DESCR, 'Record Cleanup: Student Successfully Completed ' || B.SUBJECT || ' ' || B.CATALOG_NBR || ' in ' || D.DESCR || ' Session: ' || B.SESSION_CODE
FROM PS_STDNT_GRPS_HIST A, PS_CU_CLS_TL_SE_VW B, PS_TERM_TBL D
WHERE ( D.STRM = B.STRM
AND D.ACAD_CAREER = B.ACAD_CAREER
AND D.INSTITUTION = B.INSTITUTION
AND ( A.EFFDT =
(SELECT MAX(A_ED.EFFDT) FROM PS_STDNT_GRPS_HIST A_ED
WHERE A.EMPLID = A_ED.EMPLID
AND A.INSTITUTION = A_ED.INSTITUTION
AND A.STDNT_GROUP = A_ED.STDNT_GROUP
AND A_ED.EFFDT <= SYSDATE)
AND A.INSTITUTION = :1
AND A.EFF_STATUS = 'A'
AND A.EMPLID = B.EMPLID
AND A.INSTITUTION = B.INSTITUTION
AND B.ACAD_CAREER = :2
AND B.CRSE_GRADE_INPUT IN (SELECT C.CRSE_GRADE_INPUT
FROM PS_GRADE_TBL C
WHERE C.EFFDT =
(SELECT MAX(C_ED.EFFDT) FROM PS_GRADE_TBL C_ED
WHERE C.SETID = C_ED.SETID
AND C.GRADING_SCHEME = C_ED.GRADING_SCHEME
AND C_ED.EFFDT <= SYSDATE)
AND C.SETID = :1
AND C.GRADE_CATEGORY = 'PASS')
AND B.SUBJECT = :3
AND TRIM( B.CATALOG_NBR) = TRIM(:4)
AND A.STDNT_GROUP = :5 ))
ORDER BY 7, 8, 9, 10, 11