SQL help inserting a count into existing code and using as selection criteria.
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,RO
WHERE SGBSTDN_TERM_CODE_EFF = (SELECT MAX(A.SGBSTDN_TERM_CODE_EF
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
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_fi
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!
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,RO
WHERE SGBSTDN_TERM_CODE_EFF = (SELECT MAX(A.SGBSTDN_TERM_CODE_EF
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
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_fi
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!
ASKER
Hi Mark, I am attaching a file that shows how I interpreted your recommendation and inserted into my code. The error message is described. The output from this code is merely a rorstat_pidm or ID number for the student.
ASKER
Greetings! This did not work but it may be because I did not code it they way you would have. I am attaching the entire code. I got this small bit of code to deliver the correct count. I just need to use the count in selection criteria in the same script.
The software running behind this reads a database for student pidms and then uses this script to further select the students for a failed satisfactory academic progress code.
Select the next student pidm
where the student is a graduate student
and has >= 2 C grades --like '%C%'
The following code works to give the accurate count. The count needs to keep or eliminate the student.
SELECT COUNT (*) FROM (
SELECT SHRTCKG_PIDM FROM SHRTCKG WHERE SHRTCKG_GRDE_CODE_FINAL LIKE '%C%'
AND SHRTCKG_GMOD_CODE = 'G'
AND SHRTCKG_GCHG_CODE = 'OE'
AND SHRTCKG_PIDM = 10001100
);
THe code I developed to use your solution is attached.
EE-Code-1.txt
The software running behind this reads a database for student pidms and then uses this script to further select the students for a failed satisfactory academic progress code.
Select the next student pidm
where the student is a graduate student
and has >= 2 C grades --like '%C%'
The following code works to give the accurate count. The count needs to keep or eliminate the student.
SELECT COUNT (*) FROM (
SELECT SHRTCKG_PIDM FROM SHRTCKG WHERE SHRTCKG_GRDE_CODE_FINAL LIKE '%C%'
AND SHRTCKG_GMOD_CODE = 'G'
AND SHRTCKG_GCHG_CODE = 'OE'
AND SHRTCKG_PIDM = 10001100
);
THe code I developed to use your solution is attached.
EE-Code-1.txt
Missing a SELECT
dives straight into : or 2 >= (SUM(
and should be : or 2 >= ( SELECT SUM(
dives straight into : or 2 >= (SUM(
and should be : or 2 >= ( SELECT SUM(
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you Mark! I finished testing this morning and it works well. Janis
What you can do is add another subquery to do that SUM(), but would change it a bit.... And also depends on if you want visibility of that column or not. Are you doing a group by RORSTAT_PIDM ?
e.g. SUM(case when shrtckg_gmod_code = 'G' and shrtckg_grde_code_final = 'C' then 1 else 0 end) as C_Count
Now, the table isnt currently in your joined tables, or subqueries, so will need to join it somehow. Assume that PIDM is avaliable ?
The just add in another subquery:
WHERE .....
AND 2 <= (SELECT SUM(case when shrtckg_gmod_code = 'G' and shrtckg_grde_code_final = 'C' then 1 else 0 end) as C_Count -- the CASE shouldnt be neededbut is more flexible
from shrtckg
where RORSTAT_PIDM = shrtckg_pidm -- not sure of field names....
and shrtckg_gmod_code = 'G'
and shrtckg_grde_code_final = 'C' )
or you could check "2 > " depending on wanting to include or exclude
If you could include a code block, and the version of SQL Server, we can probably make it a lot more efficient for you with actual JOINS