Description:
Generates MATHEMATICS placements for students who have TRIG or TRGCC Regents and have taken the CUNY Assessment Test in Mathematics 6 within the last two years (ignores students who already have placements active)
Query SQL:
SELECT DISTINCT CASE
WHEN J.SCORE BETWEEN 95 AND 100 AND D.SCORE BETWEEN 20 AND 120 THEN 'M261'
WHEN J.SCORE BETWEEN 93 AND 95 AND D.SCORE BETWEEN 93 AND 120 THEN 'M261'
WHEN J.SCORE BETWEEN 85 AND 92 AND D.SCORE BETWEEN 95 AND 120 THEN 'M261'
WHEN J.SCORE BETWEEN 76 AND 84 AND D.SCORE BETWEEN 100 AND 120 THEN 'M261'
WHEN J.SCORE BETWEEN 70 AND 75 AND D.SCORE BETWEEN 100 AND 120 THEN 'M261'
WHEN J.SCORE BETWEEN 60 AND 69 AND D.SCORE BETWEEN 100 AND 120 THEN 'M261'
WHEN J.SCORE BETWEEN 0 AND 59 AND D.SCORE BETWEEN 100 AND 120 THEN 'M261'
WHEN J.SCORE BETWEEN 93 AND 95 AND D.SCORE BETWEEN 20 AND 92 THEN 'M227'
WHEN J.SCORE BETWEEN 85 AND 92 AND D.SCORE BETWEEN 20 AND 94 THEN 'M227'
WHEN J.SCORE BETWEEN 76 AND 84 AND D.SCORE BETWEEN 75 AND 99 THEN 'M227'
WHEN J.SCORE BETWEEN 70 AND 75 AND D.SCORE BETWEEN 80 AND 99 THEN 'M227'
WHEN J.SCORE BETWEEN 60 AND 69 AND D.SCORE BETWEEN 80 AND 99 THEN 'M227'
WHEN J.SCORE BETWEEN 0 AND 59 AND D.SCORE BETWEEN 80 AND 99 THEN 'M227'
WHEN J.SCORE BETWEEN 76 AND 84 AND D.SCORE BETWEEN 20 AND 74 THEN 'M203'
WHEN J.SCORE BETWEEN 70 AND 75 AND D.SCORE BETWEEN 20 AND 79 THEN 'M203'
WHEN J.SCORE BETWEEN 60 AND 69 AND D.SCORE BETWEEN 55 AND 79 THEN 'M203'
WHEN J.SCORE BETWEEN 0 AND 59 AND D.SCORE BETWEEN 60 AND 79 THEN 'M203'
WHEN J.SCORE BETWEEN 60 AND 69 AND D.SCORE BETWEEN 36 AND 54 THEN 'M103'
WHEN J.SCORE BETWEEN 0 AND 59 AND D.SCORE BETWEEN 48 AND 59 THEN 'M103'
WHEN J.SCORE BETWEEN 60 AND 69 AND D.SCORE BETWEEN 20 AND 35 THEN 'M120'
WHEN J.SCORE BETWEEN 0 AND 59 AND D.SCORE BETWEEN 26 AND 47 THEN 'M120'
WHEN J.SCORE BETWEEN 0 AND 59 AND D.SCORE BETWEEN 20 AND 25 THEN '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'), D12X.XLATSHORTNAME, D.SCORE, J.TEST_ID, J.TEST_COMPONENT, J.SCORE
FROM PS_ADM_MC_VW A, PS_CU_BIO_VW C, PS_SCC_PERS_NI_QVW K, PS_STDNT_TEST_COMP D LEFT OUTER JOIN PSXLATITEM D12X ON D12X.FIELDNAME='LS_DATA_SOURCE' AND D12X.FIELDVALUE=D.LS_DATA_SOURCE AND D12X.EFF_STATUS = 'A' AND D12X.EFFDT = (SELECT MAX(EFFDT) FROM PSXLATITEM TB WHERE TB.FIELDNAME=D12X.FIELDNAME AND TB.FIELDVALUE=D12X.FIELDVALUE AND TB.EFF_STATUS = 'A' AND TB.EFFDT <= TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD') ), 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 = 'ACC-MATH-6'
AND D.LS_DATA_SOURCE IN ('S02')
AND D.TEST_COMPONENT IN ('M6')
AND D.TEST_DT = (SELECT max ( G.TEST_DT)
FROM PS_STDNT_TEST_COMP G
WHERE G.EMPLID = D.EMPLID
AND G.TEST_DT > SYSDATE - 730
AND G.TEST_ID = 'ACC-MATH-6'
AND G.TEST_COMPONENT IN ('M6')
AND G.LS_DATA_SOURCE = D.LS_DATA_SOURCE)
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','M100','NTMT')
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 ('TRGCC','TRIG'))
AND ( 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','Q','R','S','T','E')
AND F.LS_DATA_SOURCE IN ('S02'))
OR EXISTS (SELECT I.EMPLID
FROM PS_STDNT_TEST_COMP I
WHERE I.EMPLID = A.EMPLID
AND I.TEST_ID = 'CUNY-MATH'
AND I.TEST_COMPONENT = 'MAF'
AND I.LS_DATA_SOURCE IN ('S02')
AND I.SCORE_LETTER = 'PASS')
OR EXISTS (SELECT N.EMPLID
FROM PS_STDNT_TEST_COMP N
WHERE A.EMPLID = N.EMPLID
AND N.TEST_ID = 'ACC-MATH-5'
AND N.TEST_COMPONENT = 'M5'
AND N.LS_DATA_SOURCE IN ('S02')
AND N.SCORE_LETTER = 'PASS')
OR ( EXISTS (SELECT H.EMPLID
FROM PS_STDNT_TEST_COMP H
WHERE H.EMPLID = A.EMPLID
AND H.TEST_ID = 'ACT-MATH-2'
AND H.TEST_COMPONENT = 'M2'
AND H.LS_DATA_SOURCE IN ('S02')
AND H.SCORE_LETTER = 'PASS')))
AND A.EMPLID = J.EMPLID
AND J.TEST_ID = 'RGNTS'
AND J.TEST_COMPONENT IN ('TRGCC','TRIG')
AND J.SCORE = (SELECT max ( L.SCORE)
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'), D12X.XLATSHORTNAME, D.SCORE, J.TEST_ID, J.TEST_COMPONENT, J.SCORE
ORDER BY 1, 3, 4, 5