troubleshooting Question

SQL help inserting a count into existing code and using as selection criteria.

Avatar of DMKetcher
DMKetcherFlag for United States of America asked on
Microsoft SQL ServerSQL
6 Comments1 Solution102 ViewsLast Modified:
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:

SELECT RORSTAT_PIDM
FROM   RORSTAT,SGBSTDN,SHRLGPA,ROBINST

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
       (SELECT NULL
        FROM   RORSAPR
        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
from  shrtckg
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.

Thank you!
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 6 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros