Background
Gary Hotko from OTE wants to create a report of Current Students who have Service Indicator of TST/SKILL have not exempt on MATH, READ or WRITE.
This query needs to select Undergraduate students (Current Students) who meet any of the following criteria
- No WRIT EXEMPT, Tested Writing and NO Passing Score in CNY-Writing, CUNY-ESSAY and CATW
- No READ EXEMPT, Tested Reading and NO Passing Score in CNY-READING and ACT-READING
- No MATH EXEMPT, Tested Math and NO Passing Score in CNY-MATH and ACT-MATH-2
Students who meet ANY of these criteria need to have the TST-SKILL Service Indicator/Reason Code added to their record. Student MUST HAVE Tested.
Please see Kace Ticket for history reference: 48430: https://helpdesk.baruch.cuny.edu/admin
-----------
Final Report Delivery with requirements:
The query for OTE, current students CU_BAR_AD_00031_2 has been shared with Gary. The query is working fine.
CU_BAR_SR_00031_2 Prompts are below:
- Institution: Bar01
- Career: UGRD
- From Admit Term: pick any term
- To Admit Term: pick any term
- Admit Type (optional): leave blank or specific type; If you want to be more specific
Query Development:
Following table shows all necessary records that have been used to generate fields as requested by an user to create this report.
ADM_MC_VW | EMPLID, ADMIT TERM, ADMIT_TYPE, ADMIT TERM, ACAD_PROG, PROG_ACTION |
ADM_APPL_PLAN | ACAD_PLAN |
CU_BOI_VW | LAST_NAME, FIRST_NAME, MIDDLE_NAME, PHONE, EMAIL_ADDR |
STDNT_CAR_TERM |
Expressions:
:5 has been used as prompt for Admit type. If it is Null still will fetch report.
Prompt:
The admit term is coming through the stdnt_car term because this records determines if a student is term-activated or not.
Criteria:
PROG_ACTION not in list 'WADM', 'WAPP' (all other category wants in the report except the mentioned two)
ADMIT_TYPE is pulling thru Prompt and it contain number such as '2' means freshman or '3' transfer students
Reference for Service Indicator: Service Indicators
Top Level of Query:
MATH:
Does Not Exist:
SRVC_IND_DATA - Service Indicator Data is eliminating the students who have TST/SKILL
STDNT_TEST_COMP - Student Test Components is eliminating the students who have MATH EXEMPTION within some specific Test Components such as ('B','C','E','R','Q','S','T')
Exist:
STDNT_TEST_COMP - Student Test Components is looking for Students who have FAILED on 'M2', 'MAF' test component
UNION1:
READING:
Does Not Exist:
SRVC_IND_DATA - Service Indicator Data is eliminating the students who have TST/SKILL
STDNT_TEST_COMP - Student Test Components is eliminating the students who have READ EXEMPTION within some specific Test Components such as ('B','E','Q','R','S','T','X')
Exist:
STDNT_TEST_COMP - Student Test Components is looking for Students who have FAILED on ('RAF','REF') test component
UNION2:
WRITING:
Does Not Exist:
SRVC_IND_DATA - Service Indicator Data is eliminating the students who have TST/SKILL
STDNT_TEST_COMP - Student Test Components is eliminating the students who have WRIT EXEMPTION within some specific Test Components such as ('B','E','Q','R','S','T','X')
STDNT_TEST_COMP - Student Test Components is eliminating students who have PASSED on ('FINAL','WEF','WRF') test component
Exist:
STDNT_TEST_COMP - Student Test Components is looking for Students who have FAILED on ('FINAL','WEF','WRF') test component
Query Output Image:
SQL Summery:
SELECT C.LAST_NAME, C.FIRST_NAME, C.MIDDLE_NAME, A.EMPLID, A.ADMIT_TYPE, A.ADMIT_TERM, A.ACAD_PROG, B.ACAD_PLAN, A.PROG_ACTION, C.PHONE, C.EMAIL_ADDR FROM PS_ADM_MC_VW A, PS_ADM_APPL_PLAN B, PS_CU_BIO_VW C, PS_STDNT_CAR_TERM D WHERE ( A.EFFDT = (SELECT MAX(A_ED.EFFDT) FROM PS_ADM_MC_VW A_ED WHERE A.EMPLID = A_ED.EMPLID AND A.ACAD_CAREER = A_ED.ACAD_CAREER AND A.STDNT_CAR_NBR = A_ED.STDNT_CAR_NBR AND A.ADM_APPL_NBR = A_ED.ADM_APPL_NBR AND A.APPL_PROG_NBR = A_ED.APPL_PROG_NBR AND A_ED.EFFDT <= SYSDATE) AND A.EFFSEQ = (SELECT MAX(A_ES.EFFSEQ) FROM PS_ADM_MC_VW A_ES WHERE A.EMPLID = A_ES.EMPLID AND A.ACAD_CAREER = A_ES.ACAD_CAREER AND A.STDNT_CAR_NBR = A_ES.STDNT_CAR_NBR AND A.ADM_APPL_NBR = A_ES.ADM_APPL_NBR AND A.APPL_PROG_NBR = A_ES.APPL_PROG_NBR AND A.EFFDT = A_ES.EFFDT) AND A.EMPLID = B.EMPLID AND A.ACAD_CAREER = B.ACAD_CAREER AND A.STDNT_CAR_NBR = B.STDNT_CAR_NBR AND A.ADM_APPL_NBR = B.ADM_APPL_NBR AND A.APPL_PROG_NBR = B.APPL_PROG_NBR AND A.EFFSEQ = B.EFFSEQ AND A.EFFDT = B.EFFDT AND A.INSTITUTION = :1 AND A.ACAD_CAREER = :2 AND A.PROG_ACTION NOT IN ('WADM','WAPP') AND A.EMPLID = C.EMPLID AND A.EMPLID = D.EMPLID AND A.ACAD_CAREER = D.ACAD_CAREER AND A.STDNT_CAR_NBR = D.STDNT_CAR_NBR AND D.INSTITUTION = A.INSTITUTION AND D.STRM BETWEEN :3 AND :4 AND ( A.ADMIT_TYPE = :5 OR :5 IS NULL) AND NOT EXISTS (SELECT E.EMPLID FROM PS_SRVC_IND_DATA E WHERE ( E.EMPLID = A.EMPLID AND E.INSTITUTION = A.INSTITUTION AND E.SRVC_IND_CD = 'TST' AND E.SRVC_IND_REASON = 'SKILL' )) AND ( NOT EXISTS (SELECT F.EMPLID FROM PS_STDNT_TEST_COMP F WHERE ( F.EMPLID = A.EMPLID AND F.TEST_ID = 'MATH EXEMPT' AND F.TEST_COMPONENT IN ('B','C','E','R','Q','S','T') )) AND ( NOT EXISTS (SELECT G.EMPLID FROM PS_STDNT_TEST_COMP G WHERE ( G.EMPLID = A.EMPLID AND G.TEST_COMPONENT IN ('M2','MAF') AND G.SCORE_LETTER = 'PASS' )) AND EXISTS (SELECT H.EMPLID FROM PS_STDNT_TEST_COMP H WHERE ( H.EMPLID = A.EMPLID AND H.TEST_COMPONENT IN ('M2','MAF') AND H.SCORE_LETTER = 'FAIL' )))) ) UNION SELECT K.LAST_NAME, K.FIRST_NAME, K.MIDDLE_NAME, I.EMPLID, I.ADMIT_TYPE, I.ADMIT_TERM, I.ACAD_PROG, J.ACAD_PLAN, I.PROG_ACTION, K.PHONE, K.EMAIL_ADDR FROM PS_ADM_MC_VW I, PS_ADM_APPL_PLAN J, PS_CU_BIO_VW K, PS_STDNT_CAR_TERM L WHERE ( I.EFFDT = (SELECT MAX(I_ED.EFFDT) FROM PS_ADM_MC_VW I_ED WHERE I.EMPLID = I_ED.EMPLID AND I.ACAD_CAREER = I_ED.ACAD_CAREER AND I.STDNT_CAR_NBR = I_ED.STDNT_CAR_NBR AND I.ADM_APPL_NBR = I_ED.ADM_APPL_NBR AND I.APPL_PROG_NBR = I_ED.APPL_PROG_NBR AND I_ED.EFFDT <= SYSDATE) AND I.EFFSEQ = (SELECT MAX(I_ES.EFFSEQ) FROM PS_ADM_MC_VW I_ES WHERE I.EMPLID = I_ES.EMPLID AND I.ACAD_CAREER = I_ES.ACAD_CAREER AND I.STDNT_CAR_NBR = I_ES.STDNT_CAR_NBR AND I.ADM_APPL_NBR = I_ES.ADM_APPL_NBR AND I.APPL_PROG_NBR = I_ES.APPL_PROG_NBR AND I.EFFDT = I_ES.EFFDT) AND I.EMPLID = J.EMPLID AND I.ACAD_CAREER = J.ACAD_CAREER AND I.STDNT_CAR_NBR = J.STDNT_CAR_NBR AND I.ADM_APPL_NBR = J.ADM_APPL_NBR AND I.APPL_PROG_NBR = J.APPL_PROG_NBR AND J.EFFSEQ = I.EFFSEQ AND J.EFFDT = I.EFFDT AND I.INSTITUTION = :1 AND I.ACAD_CAREER = :2 AND I.PROG_ACTION NOT IN ('WADM','WAPP') AND I.EMPLID = K.EMPLID AND I.EMPLID = L.EMPLID AND I.ACAD_CAREER = L.ACAD_CAREER AND I.STDNT_CAR_NBR = L.STDNT_CAR_NBR AND L.INSTITUTION = I.INSTITUTION AND L.STRM BETWEEN :3 AND :4 AND ( I.ADMIT_TYPE = :5 OR :5 IS NULL) AND NOT EXISTS (SELECT M.EMPLID FROM PS_SRVC_IND_DATA M WHERE ( M.EMPLID = I.EMPLID AND M.INSTITUTION = I.INSTITUTION AND M.SRVC_IND_CD = 'TST' AND M.SRVC_IND_REASON = 'SKILL' )) AND ( NOT EXISTS (SELECT N.EMPLID FROM PS_STDNT_TEST_COMP N WHERE ( N.EMPLID = I.EMPLID AND N.TEST_ID = 'READ EXEMPT' AND N.TEST_COMPONENT IN ('B','E','Q','R','S','T','X') )) AND ( NOT EXISTS (SELECT O.EMPLID FROM PS_STDNT_TEST_COMP O WHERE ( O.EMPLID = I.EMPLID AND O.TEST_COMPONENT IN ('RAF','REF') AND O.SCORE_LETTER = 'PASS' )) AND EXISTS (SELECT P.EMPLID FROM PS_STDNT_TEST_COMP P WHERE ( P.EMPLID = I.EMPLID AND P.TEST_COMPONENT IN ('REF','RAF') AND P.SCORE_LETTER = 'FAIL' )))) ) UNION SELECT S.LAST_NAME, S.FIRST_NAME, S.MIDDLE_NAME, Q.EMPLID, Q.ADMIT_TYPE, Q.ADMIT_TERM, Q.ACAD_PROG, R.ACAD_PLAN, Q.PROG_ACTION, S.PHONE, S.EMAIL_ADDR FROM PS_ADM_MC_VW Q, PS_ADM_APPL_PLAN R, PS_CU_BIO_VW S, PS_STDNT_CAR_TERM T WHERE ( Q.EFFDT = (SELECT MAX(Q_ED.EFFDT) FROM PS_ADM_MC_VW Q_ED WHERE Q.EMPLID = Q_ED.EMPLID AND Q.ACAD_CAREER = Q_ED.ACAD_CAREER AND Q.STDNT_CAR_NBR = Q_ED.STDNT_CAR_NBR AND Q.ADM_APPL_NBR = Q_ED.ADM_APPL_NBR AND Q.APPL_PROG_NBR = Q_ED.APPL_PROG_NBR AND Q_ED.EFFDT <= SYSDATE) AND Q.EFFSEQ = (SELECT MAX(Q_ES.EFFSEQ) FROM PS_ADM_MC_VW Q_ES WHERE Q.EMPLID = Q_ES.EMPLID AND Q.ACAD_CAREER = Q_ES.ACAD_CAREER AND Q.STDNT_CAR_NBR = Q_ES.STDNT_CAR_NBR AND Q.ADM_APPL_NBR = Q_ES.ADM_APPL_NBR AND Q.APPL_PROG_NBR = Q_ES.APPL_PROG_NBR AND Q.EFFDT = Q_ES.EFFDT) AND Q.EMPLID = R.EMPLID AND Q.ACAD_CAREER = R.ACAD_CAREER AND Q.STDNT_CAR_NBR = R.STDNT_CAR_NBR AND Q.ADM_APPL_NBR = R.ADM_APPL_NBR AND Q.APPL_PROG_NBR = R.APPL_PROG_NBR AND R.EFFSEQ = Q.EFFSEQ AND R.EFFDT = Q.EFFDT AND Q.INSTITUTION = :1 AND Q.ACAD_CAREER = :2 AND Q.PROG_ACTION NOT IN ('WADM','WAPP') AND Q.EMPLID = S.EMPLID AND Q.EMPLID = T.EMPLID AND Q.ACAD_CAREER = T.ACAD_CAREER AND Q.STDNT_CAR_NBR = T.STDNT_CAR_NBR AND T.INSTITUTION = Q.INSTITUTION AND T.STRM BETWEEN :3 AND :4 AND ( Q.ADMIT_TYPE = :5 OR :5 IS NULL) AND NOT EXISTS (SELECT U.EMPLID FROM PS_SRVC_IND_DATA U WHERE ( U.EMPLID = Q.EMPLID AND U.INSTITUTION = Q.INSTITUTION AND U.SRVC_IND_CD = 'TST' AND U.SRVC_IND_REASON = 'SKILL' )) AND ( NOT EXISTS (SELECT V.EMPLID FROM PS_STDNT_TEST_COMP V WHERE ( V.EMPLID = Q.EMPLID AND V.TEST_ID = 'WRIT EXEMPT' AND V.TEST_COMPONENT IN ('B','E','Q','R','S','T','X') )) AND ( NOT EXISTS (SELECT W.EMPLID FROM PS_STDNT_TEST_COMP W WHERE ( W.EMPLID = Q.EMPLID AND W.SCORE_LETTER = 'PASS' AND W.TEST_COMPONENT IN ('FINAL','WEF','WRF') )) AND EXISTS (SELECT X.EMPLID FROM PS_STDNT_TEST_COMP X WHERE ( X.EMPLID = Q.EMPLID AND X.TEST_COMPONENT IN ('WRF','WEF','FINAL') AND X.SCORE_LETTER = 'FAIL' )))) ) ORDER BY 1, 2, 3 |