Background
The admission has requested us to modify some of their queries and create some new queries to serve their purpose for new applicant for Baruch College (CUNY). Attached is the documentation that has provided by admission, please see below:
Math Placements: CU_BAR_AD-00006A_1 and CUN_BAR_AD_00006B_2 following issues need to be addressed:
- Remove duplication
- should return most recent record
- Student who passed 'ACT-MATH-2' doesn't need to correspond 'ACT-MATH-1'. Therefore, exclude 'ACT-MATH-1' from criteria.
For a original query detail; see the link CU_BAR_AD_00006A_1 and CU_BAR_AD_00006B_2
The new queries CU_BAR_AD_00006A_3 and CU_BAR_AD_00006B_4 have described below:
Final Report Delivery with requirements:
The queries have delivered to Gary and Dennyse. Initially we have submitted the report based on their requirements and it has been approved by them. Please see the detail in kace system: https://helpdesk.baruch.cuny.edu/admin The ticket number is 46135 .
---------
Query Development:
Following table shows all necessary records that have been used to generate fields as requested by an user to create this report.
Records | Description of fields |
|---|---|
| ADM_MC_VW | EMPLID, ADMIT TYPE |
| CU_BOI_VW | LAST_NAME, FIRST_NAME, MIDDLE_NAME, PHONE, EMAIL_ADDR |
| SCC_PERS_NI_QVW | SSN |
| STDNT_TEST_COMP | TEST ID, TEST DT, DATA SRCE, TOTAL SCORE, SG |
| SUB-QUEREIES: | |
| STDNT_TEST_COMP | max (J.TEST_DT) |
| STDNT_GRPS_HIST | B.EMPLID - Empl ID (DOESN'T EXIST sg ('M1','M2'), NTMT, S100 ) |
| STDNT_TEST_COMP | BLANK (DOESN'T EXIST 'SMA3','MATHB' test component) |
| STDNT_TEST_COMP | F.EMPLID - Empl ID (EXIST test id 'MATH EXEMPT') |
| STDNT_TEST_COMP | I.EMPLID - Empl ID (EXIST test-com 'MAF', test s > 25) |
| STDNT_TEST_COMP | G.EMPLID - Empl ID (EXIST test-com 'M1', test s > 45) |
| STDNT_TEST_COMP | H.EMPLID - Empl ID (EXIST test-com 'M2', test s > 40) |
Expressions:
Few 'Expressions' have been added to find Student Group 'SG', 'SYSDATE -730', SUM (D.SCORE), ':5' have described the prompt as null and define the value of 'max (J.TEST_DT)' to find latest date.
Prompt:
Institution, Career, From Admit Term, To Admit Term have been mandatory for an user to input and Admit Type is optional to extract more precise data.
Criteria:
The following criteria needs to be removed because students who passed ACT-MATH-2 do not have to correspond ACT-MATH-1 proficient.
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)
The following criteria has been added as a subquery for find latest date.
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
Query SQL:
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
Query SQL:
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