Legend of this query:

Image of the query:

 

 
SELECT CASE
WHEN E.TEST_COMPONENT = 'FINAL' AND F.CU_ESL_INDICATOR <> 'Y' AND E.SCORE > 55 THEN 'E210'
WHEN E.TEST_COMPONENT = 'WEF' AND F.CU_ESL_INDICATOR <> 'Y' AND E.SCORE > 6 THEN 'E210'
WHEN E.TEST_COMPONENT = 'WRF' AND F.CU_ESL_INDICATOR <> 'Y' AND E.SCORE > 7 THEN 'E210'
WHEN E.TEST_COMPONENT = 'FINAL' AND F.CU_ESL_INDICATOR = 'Y' AND E.SCORE > 71 THEN 'E210'
WHEN E.TEST_COMPONENT = 'WEF' AND F.CU_ESL_INDICATOR = 'Y' AND E.SCORE > 8 THEN 'E210'
WHEN E.TEST_COMPONENT = 'WRF' AND F.CU_ESL_INDICATOR = 'Y' AND E.SCORE > 8 THEN 'E210'
WHEN E.TEST_COMPONENT = 'FINAL' AND F.CU_ESL_INDICATOR = 'Y' AND E.SCORE BETWEEN 56 AND 71 THEN 'E21T'
WHEN E.TEST_COMPONENT = 'WEF' AND F.CU_ESL_INDICATOR = 'Y' AND E.SCORE BETWEEN 7 AND 8 THEN 'E21T'
WHEN E.TEST_COMPONENT = 'WRF' AND F.CU_ESL_INDICATOR = 'Y' AND E.SCORE = 8 THEN 'E21T'
WHEN E.TEST_COMPONENT = 'FINAL' AND F.CU_ESL_INDICATOR = 'Y' AND E.SCORE BETWEEN 44 AND 55 THEN 'E132'
WHEN E.TEST_COMPONENT = 'WEF' AND F.CU_ESL_INDICATOR = 'Y' AND E.SCORE = 6 THEN 'E132'
WHEN E.TEST_COMPONENT = 'WRF' AND F.CU_ESL_INDICATOR = 'Y' AND E.SCORE BETWEEN 6 AND 7 THEN 'E132'
ELSE 'NOEN'
END, A.EMPLID, C.LAST_NAME, C.FIRST_NAME, C.MIDDLE_NAME, K.NATIONAL_ID, A.ADMIT_TYPE, C.PHONE, C.EMAIL_ADDR, E.TEST_ID, E.TEST_COMPONENT, E.SCORE, TO_CHAR(E.TEST_DT,'YYYY-MM-DD'), F.CU_ESL_INDICATOR
FROM PS_ADM_MC_VW A, PS_CU_BIO_VW C, PS_STDNT_TEST_COMP E, PS_SCC_PERS_NI_QVW K, PS_CU_ST_TEST_COMP F
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 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 ('E210','E21T','E132','NOEN')
AND B.EFF_STATUS = 'A')
AND NOT EXISTS (SELECT D.EMPLID
FROM PS_STDNT_TEST_COMP D
WHERE D.EMPLID = A.EMPLID
AND D.TEST_ID = 'WRIT EXEMPT')
AND E.EMPLID = A.EMPLID
AND E.EMPLID = F.EMPLID (+)
AND E.TEST_ID = F.TEST_ID (+)
AND E.TEST_COMPONENT = F.TEST_COMPONENT (+)
AND E.TEST_DT = F.TEST_DT (+)
AND E.LS_DATA_SOURCE = F.LS_DATA_SOURCE (+)
AND F.TEST_DT = (SELECT MAX ( T.TEST_DT)
FROM PS_STDNT_TEST_COMP T
WHERE T.EMPLID = E.EMPLID
AND T.TEST_COMPONENT IN ('FINAL','WEF','WRF'))
AND (( E.TEST_ID = 'CNY-WRITING'
AND E.TEST_COMPONENT = 'WRF')
OR ( E.TEST_ID = 'CATW'
AND E.TEST_COMPONENT = 'FINAL')
OR ( E.TEST_ID = 'CUNY-ESSAY'
AND E.TEST_COMPONENT = 'WEF'))
UNION
SELECT 'E210', M.EMPLID, N.LAST_NAME, N.FIRST_NAME, N.MIDDLE_NAME, O.NATIONAL_ID, M.ADMIT_TYPE, N.PHONE, N.EMAIL_ADDR, P.TEST_ID, ' ', 0, ' ', ' '
FROM PS_ADM_MC_VW M, PS_CU_BIO_VW N, PS_SCC_PERS_NI_QVW O, PS_STDNT_TEST_COMP P
WHERE M.EFFDT =
(SELECT MAX(M_ED.EFFDT) FROM PS_ADM_MC_VW M_ED
WHERE M.EMPLID = M_ED.EMPLID
AND M.ACAD_CAREER = M_ED.ACAD_CAREER
AND M.STDNT_CAR_NBR = M_ED.STDNT_CAR_NBR
AND M.ADM_APPL_NBR = M_ED.ADM_APPL_NBR
AND M.APPL_PROG_NBR = M_ED.APPL_PROG_NBR
AND M_ED.EFFDT <= SYSDATE)
AND M.EFFSEQ =
(SELECT MAX(M_ES.EFFSEQ) FROM PS_ADM_MC_VW M_ES
WHERE M.EMPLID = M_ES.EMPLID
AND M.ACAD_CAREER = M_ES.ACAD_CAREER
AND M.STDNT_CAR_NBR = M_ES.STDNT_CAR_NBR
AND M.ADM_APPL_NBR = M_ES.ADM_APPL_NBR
AND M.APPL_PROG_NBR = M_ES.APPL_PROG_NBR
AND M.EFFDT = M_ES.EFFDT)
AND M.INSTITUTION = :1
AND M.ACAD_CAREER = :2
AND M.ADMIT_TERM BETWEEN :3 AND :4
AND M.ADMIT_TYPE = :5
AND M.EMPLID = N.EMPLID
AND M.EMPLID = O.EMPLID
AND M.EMPLID = P.EMPLID
AND P.TEST_ID = 'WRIT EXEMPT'
AND NOT EXISTS (SELECT Q.EMPLID
FROM PS_STDNT_GRPS_HIST Q
WHERE Q.EFFDT =
(SELECT MAX(Q_ED.EFFDT) FROM PS_STDNT_GRPS_HIST Q_ED
WHERE Q.EMPLID = Q_ED.EMPLID
AND Q.INSTITUTION = Q_ED.INSTITUTION
AND Q.STDNT_GROUP = Q_ED.STDNT_GROUP
AND Q_ED.EFFDT <= SYSDATE)
AND Q.EMPLID = M.EMPLID
AND Q.INSTITUTION = Q.INSTITUTION
AND Q.EFF_STATUS = 'A'
AND Q.STDNT_GROUP IN ('E210','E21T','E132','NOEN'))
ORDER BY 3, 4, 5
  • No labels