...
The new query is CU_BAR_AD_00006A_3 and CU_BAR_AD_00006A00006B_3CU_BAR_AD_00006A_34 Anchor
Anchor
Final Report Delivery with requirements:
...
No Format |
---|
AND D.TEST_DT = (SELECT max ( J.TEST_DT) FROM PS_STDNT_TEST_COMP J WHERE J.EMPLID = D.EMPLID AND J.TEST_DT > SYSDATE - 730 AND J.TEST_COMPONENT IN ('MPL2','MPL3','MPL5') AND J.LS_DATA_SOURCE = D.LS_DATA_SOURCE) |
Image of CU_BAR_AD_00006A_3
Anchor | ||||
---|---|---|---|---|
|
Query SQL:
Panel |
---|
SELECT CASE WHEN SUM ( D.SCORE ) > 240 THEN 'M261' WHEN SUM ( D.SCORE) BETWEEN 211 AND 240 THEN 'M227' WHEN SUM ( D.SCORE) BETWEEN 148 AND 210 THEN 'M203' WHEN SUM ( D.SCORE) BETWEEN 66 AND 147 THEN 'M103' WHEN SUM ( D.SCORE) BETWEEN 24 AND 65 THEN 'M120' WHEN SUM ( D.SCORE) BETWEEN 0 AND 23 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'), D.LS_DATA_SOURCE, SUM ( D.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 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 = (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_COMPONENT IN ('MPL2','MPL3','MPL5') 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 ( substr ( B.STDNT_GROUP, 1, 2) IN ('M1','M2') OR B.STDNT_GROUP = 'NTMT' OR B.STDNT_GROUP = 'S100') AND B.EFF_STATUS = 'A') AND NOT 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 H.EMPLID FROM PS_STDNT_TEST_COMP H WHERE H.EMPLID = A.EMPLID AND H.TEST_COMPONENT = 'M2' AND H.SCORE >= 40))) 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 ORDER BY 1, 3, 4, 5 |
The image of CU_BAR_AD_00006B_4 Anchor CU_BAR_AD_00006B_4 CU_BAR_AD_00006B_4
CU_BAR_AD_00006B_4 | |
CU_BAR_AD_00006B_4 |
Query SQL:
Panel |
---|
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 = (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_COMPONENT IN ('MPL2','MPL3','MPL5') 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 ('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 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 |
...