I need assistance with inserting a count into some working SQL code for Satisfactory Academic Progress processing to identify graduate students who have more than two C grades. The code currently selects students who have a GPA below 3.0, attempted credits >= 15 and are not already in a warning group. The 'C' selection criteria is new.
The only output from this code is RORSTAT_PIDM. The student is selected or they are not.
The working code is:
WHERE SGBSTDN_TERM_CODE_EFF = (SELECT MAX(A.SGBSTDN_TERM_CODE_EFF) --find student's current level of undergrad or graduate
FROM SGBSTDN A
WHERE A.SGBSTDN_PIDM = RORSTAT_PIDM
AND RORSTAT_AIDY_CODE = ROBINST_AIDY_CODE
AND A.SGBSTDN_TERM_CODE_EFF <= ROBINST_CURRENT_TERM_CODE)
AND SGBSTDN_LEVL_CODE IN ('GR','DC') -- select if student is in a doctoral or graduate program
AND SGBSTDN_LEVL_CODE = SHRLGPA_LEVL_CODE --link levels to get the correct hours and GPA for the level
AND SHRLGPA_GPA_TYPE_IND = 'O' --select overall grade data
AND SHRLGPA_HOURS_ATTEMPTED >= 15
AND SHRLGPA_GPA < 3.00
AND (NOT EXISTS -- exclude if student is already in one of the RORSAPR groups below
WHERE RORSAPR_TERM_CODE < ROBINST_CURRENT_TERM_CODE
AND RORSAPR_SAPR_CODE IN ('M','MA','MG','FPACE','FB','FGPA','WGPA','WGR','WPACE','F','FG','P','PA','PG','AD','ARC','A','AS','AP','AI')
AND RORSAPR_PIDM = RORSTAT_PIDM) )
AND RORSTAT_PIDM = SHRLGPA_PIDM
AND RORSTAT_PIDM = SGBSTDN_PIDM
AND RORSTAT_AIDY_CODE = :AIDY
AND RORSTAT_PIDM = :PIDM
The basic code for the count that works as a stand alone script is:
select count(shrtckg_grde_code_final) as C_count
where shrtckg_gmod_code = 'G'
and shrtckg_grde_code_final = 'C'
If the count is >= 2 the pidm will be selected.
Please let me know if you would rather this request be put in an attachment. I worked with this group a long time ago and I can't remember how I got the quickest answer.