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!
Last Comment
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
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thank you Mark! I finished testing this morning and it works well. Janis
Microsoft SQL Server
Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.
171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
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