Legend of the Query:

Results

 

 

 
SELECT CASE
WHEN J.SCORE > 95 THEN 'M261'
WHEN J.SCORE BETWEEN 85 AND 95
AND SUM ( D.SCORE) BETWEEN 211 AND 300 THEN 'M261'
WHEN J.SCORE BETWEEN 85 AND 95
AND SUM ( D.SCORE) BETWEEN 0 AND 210 THEN 'M227'
WHEN J.SCORE BETWEEN 70 AND 84
AND SUM ( D.SCORE) BETWEEN 185 AND 300 THEN 'M227'
WHEN J.SCORE BETWEEN 70 AND 84
AND SUM ( D.SCORE) BETWEEN 0 AND 184 THEN 'M203'
WHEN J.SCORE BETWEEN 60 AND 69
AND SUM ( D.SCORE) BETWEEN 148 AND 300 THEN 'M203'
WHEN J.SCORE BETWEEN 60 AND 69
AND SUM ( D.SCORE) BETWEEN 0 AND 147 THEN 'M103'
WHEN J.SCORE BETWEEN 0 AND 59
AND SUM ( D.SCORE) BETWEEN 241 AND 300 THEN 'M261'
WHEN J.SCORE BETWEEN 0 AND 59
AND SUM ( D.SCORE) BETWEEN 211 AND 240 THEN 'M227'
WHEN J.SCORE BETWEEN 0 AND 59
AND SUM ( D.SCORE) BETWEEN 148 AND 210 THEN 'M203'
WHEN J.SCORE BETWEEN 0 AND 59
AND SUM ( D.SCORE) BETWEEN 66 AND 147 THEN 'M103'
WHEN J.SCORE BETWEEN 0 AND 59
AND SUM ( D.SCORE) BETWEEN 24 AND 65 THEN 'M120'
ELSE 'MATH'
END, A.EMPLID, C.LAST_NAME, C.FIRST_NAME, C.MIDDLE_NAME, K.NATIONAL_ID, A.ADMIT_TYPE, C.PHONE, C.EMAIL_ADDR, D.TEST_ID, TO_CHAR(D.TEST_DT,'YYYY-MM-DD'), D.LS_DATA_SOURCE, SUM ( D.SCORE), J.TEST_ID, J.TEST_COMPONENT, J.SCORE, TO_CHAR(SYSDATE,'YYYY-MM-DD')
FROM PS_ADM_MC_VW A, PS_CU_BIO_VW C, PS_SCC_PERS_NI_QVW K, PS_STDNT_TEST_COMP D, PS_STDNT_TEST_COMP J
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.INSTITUTION = :1
AND A.ACAD_CAREER = :2
AND A.ADMIT_TERM BETWEEN :3 AND :4
AND ( A.ADMIT_TYPE = :5
OR :5 IS NULL)
AND A.ACAD_PROG = 'UGRD'
AND A.EMPLID = C.EMPLID
AND A.EMPLID = K.EMPLID
AND A.EMPLID = D.EMPLID
AND D.TEST_ID = 'ACT-MATH-3'
AND D.LS_DATA_SOURCE = 'S02'
AND D.TEST_COMPONENT IN ('MPL2','MPL3','MPL5')
AND D.TEST_DT > SYSDATE - 730
AND NOT EXISTS (SELECT B.EMPLID
FROM PS_STDNT_GRPS_HIST B
WHERE ( B.EFFDT =
(SELECT MAX(B_ED.EFFDT) FROM PS_STDNT_GRPS_HIST B_ED
WHERE B.EMPLID = B_ED.EMPLID
AND B.INSTITUTION = B_ED.INSTITUTION
AND B.STDNT_GROUP = B_ED.STDNT_GROUP
AND B_ED.EFFDT <= SYSDATE)
AND B.EMPLID = A.EMPLID
AND B.INSTITUTION = A.INSTITUTION
AND B.STDNT_GROUP IN ('S100','M120','M103','M203','M227','M261')
AND B.EFF_STATUS = 'A' ))
AND EXISTS (SELECT 'X'
FROM PS_STDNT_TEST_COMP E
WHERE ( E.EMPLID = A.EMPLID
AND E.TEST_ID = 'RGNTS'
AND E.TEST_COMPONENT IN ('SMA3','MATHB') ))
AND ( EXISTS (SELECT F.EMPLID
FROM PS_STDNT_TEST_COMP F
WHERE ( F.EMPLID = A.EMPLID
AND F.TEST_ID = 'MATH EXEMPT' ))
OR EXISTS (SELECT I.EMPLID
FROM PS_STDNT_TEST_COMP I
WHERE ( I.EMPLID = A.EMPLID
AND I.TEST_COMPONENT = 'MAF'
AND I.SCORE >= 25 ))
OR ( EXISTS (SELECT G.EMPLID
FROM PS_STDNT_TEST_COMP G
WHERE ( G.EMPLID = A.EMPLID
AND G.TEST_COMPONENT = 'M1'
AND G.SCORE >= 45 ))
AND EXISTS (SELECT H.EMPLID
FROM PS_STDNT_TEST_COMP H
WHERE ( H.EMPLID = A.EMPLID
AND H.TEST_COMPONENT = 'M2'
AND H.SCORE >= 40 ))))
AND A.EMPLID = J.EMPLID
AND J.TEST_ID = 'RGNTS'
AND J.TEST_COMPONENT IN ('SMA3','MATHB')
AND J.TEST_DT = (SELECT max ( L.TEST_DT)
FROM PS_STDNT_TEST_COMP L
WHERE ( L.EMPLID = J.EMPLID
AND L.TEST_ID = J.TEST_ID
AND L.TEST_COMPONENT = J.TEST_COMPONENT )) )
GROUP BY A.EMPLID, C.LAST_NAME, C.FIRST_NAME, C.MIDDLE_NAME, K.NATIONAL_ID, A.ADMIT_TYPE, C.PHONE, C.EMAIL_ADDR, D.TEST_ID, TO_CHAR(D.TEST_DT,'YYYY-MM-DD'), D.LS_DATA_SOURCE, J.TEST_ID, J.TEST_COMPONENT, J.SCORE
ORDER BY 1, 3, 4, 5

 

 

 

  • No labels