Background

Gary Hotko from OTE wants to create a report of Current Students who have Service Indicator of TST/SKILL have not exempt on MATH, READ or WRITE.

 

This query needs to select Undergraduate students (Current Students) who meet any of the following criteria

  1.  No WRIT EXEMPT, Tested Writing and NO Passing Score in CNY-Writing, CUNY-ESSAY and CATW
  2. No READ EXEMPT, Tested Reading and NO Passing Score in CNY-READING and ACT-READING
  3.  No MATH EXEMPT, Tested Math and NO Passing Score in CNY-MATH and ACT-MATH-2

 Students who meet ANY of these criteria need to have the TST-SKILL Service Indicator/Reason Code added to their record. Student MUST HAVE Tested.

Please see Kace Ticket for history reference: 48430:  https://helpdesk.baruch.cuny.edu/admin


----------- 

Final Report Delivery with requirements:

 The query for OTE,   current students CU_BAR_AD_00031_2 has been shared with Gary. The query is working fine. 

 CU_BAR_SR_00031_2 Prompts are below:  

  • Institution: Bar01 
  • Career: UGRD 
  • From Admit Term: pick any term
  • To Admit Term:  pick any term
  • Admit Type (optional): leave blank or specific type; If you want to be more specific 

 


 

Query Development:

 

Following table shows all necessary records that have been used to generate fields as requested by an user to create this report.

 

 ADM_MC_VW EMPLID, ADMIT TERM, ADMIT_TYPE,

ADMIT TERM, ACAD_PROG, PROG_ACTION

ADM_APPL_PLAN ACAD_PLAN
CU_BOI_VWLAST_NAME, FIRST_NAME, MIDDLE_NAME, PHONE, EMAIL_ADDR
STDNT_CAR_TERM  

 

Expressions:

 

:5 has been used as prompt for Admit type.  If it is Null still will fetch report.

 

Prompt:

 

 The admit term is coming through the stdnt_car term because this records determines if a student is term-activated or not.

Criteria:

 PROG_ACTION not in list 'WADM', 'WAPP' (all other category wants in the report except the mentioned two)

ADMIT_TYPE is pulling thru Prompt and it contain number such as '2' means freshman or '3' transfer students

Reference for Service Indicator: Service Indicators

Top Level of Query:

MATH:

Does Not Exist:

 SRVC_IND_DATA - Service Indicator Data is eliminating the students who have TST/SKILL

STDNT_TEST_COMP - Student Test Components is eliminating the students who have MATH EXEMPTION within some specific Test Components  such as ('B','C','E','R','Q','S','T')


STDNT_TEST_COMP - Student Test Components is eliminating students who have PASSED on 'M2', 'MAF' test component

Exist:

STDNT_TEST_COMP - Student Test Components is looking for Students who have FAILED on 'M2', 'MAF' test component


UNION1:

READING:

 

Does Not Exist:

 

 SRVC_IND_DATA - Service Indicator Data is eliminating the students who have TST/SKILL

 

STDNT_TEST_COMP - Student Test Components is eliminating the students who have READ EXEMPTION within some specific Test Components  such as ('B','E','Q','R','S','T','X')

 

 

STDNT_TEST_COMP - Student Test Components is eliminating students who have PASSED on ('RAF','REF') test component

 

Exist:

 

STDNT_TEST_COMP - Student Test Components is looking for Students who have FAILED on ('RAF','REF') test component

 


UNION2:

 

WRITING:

 

 Does Not Exist:

 

 SRVC_IND_DATA - Service Indicator Data is eliminating the students who have TST/SKILL

 

 STDNT_TEST_COMP - Student Test Components is eliminating the students who have WRIT EXEMPTION within some specific Test Components  such as ('B','E','Q','R','S','T','X')

 

 STDNT_TEST_COMP - Student Test Components is eliminating students who have PASSED on ('FINAL','WEF','WRF') test component

 

 Exist:

 

 STDNT_TEST_COMP - Student Test Components is looking for Students who have FAILED on ('FINAL','WEF','WRF') test component

Query Output Image:

SQL Summery:

SELECT C.LAST_NAME, C.FIRST_NAME, C.MIDDLE_NAME, A.EMPLID, A.ADMIT_TYPE, A.ADMIT_TERM, A.ACAD_PROG, B.ACAD_PLAN, A.PROG_ACTION, C.PHONE, C.EMAIL_ADDR
  FROM PS_ADM_MC_VW A, PS_ADM_APPL_PLAN B, PS_CU_BIO_VW C, PS_STDNT_CAR_TERM 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.EMPLID = B.EMPLID
     AND A.ACAD_CAREER = B.ACAD_CAREER
     AND A.STDNT_CAR_NBR = B.STDNT_CAR_NBR
     AND A.ADM_APPL_NBR = B.ADM_APPL_NBR
     AND A.APPL_PROG_NBR = B.APPL_PROG_NBR
     AND A.EFFSEQ = B.EFFSEQ
     AND A.EFFDT = B.EFFDT
     AND A.INSTITUTION = :1
     AND A.ACAD_CAREER = :2
     AND A.PROG_ACTION NOT IN ('WADM','WAPP')
     AND A.EMPLID = C.EMPLID
     AND A.EMPLID = D.EMPLID
     AND A.ACAD_CAREER = D.ACAD_CAREER
     AND A.STDNT_CAR_NBR = D.STDNT_CAR_NBR
     AND D.INSTITUTION = A.INSTITUTION
     AND D.STRM BETWEEN :3 AND :4
     AND ( A.ADMIT_TYPE = :5
     OR :5 IS NULL)
     AND NOT EXISTS (SELECT E.EMPLID
  FROM PS_SRVC_IND_DATA E
  WHERE ( E.EMPLID = A.EMPLID
     AND E.INSTITUTION = A.INSTITUTION
     AND E.SRVC_IND_CD = 'TST'
     AND E.SRVC_IND_REASON = 'SKILL' ))
     AND ( NOT 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','E','R','Q','S','T') ))
     AND ( NOT EXISTS (SELECT G.EMPLID
  FROM PS_STDNT_TEST_COMP G
  WHERE ( G.EMPLID = A.EMPLID
     AND G.TEST_COMPONENT IN ('M2','MAF')
     AND G.SCORE_LETTER = 'PASS' ))
     AND EXISTS (SELECT H.EMPLID
  FROM PS_STDNT_TEST_COMP H
  WHERE ( H.EMPLID = A.EMPLID
     AND H.TEST_COMPONENT IN ('M2','MAF')
     AND H.SCORE_LETTER = 'FAIL' )))) )
UNION
SELECT K.LAST_NAME, K.FIRST_NAME, K.MIDDLE_NAME, I.EMPLID, I.ADMIT_TYPE, I.ADMIT_TERM, I.ACAD_PROG, J.ACAD_PLAN, I.PROG_ACTION, K.PHONE, K.EMAIL_ADDR
  FROM PS_ADM_MC_VW I, PS_ADM_APPL_PLAN J, PS_CU_BIO_VW K, PS_STDNT_CAR_TERM L
  WHERE ( I.EFFDT =
        (SELECT MAX(I_ED.EFFDT) FROM PS_ADM_MC_VW I_ED
        WHERE I.EMPLID = I_ED.EMPLID
          AND I.ACAD_CAREER = I_ED.ACAD_CAREER
          AND I.STDNT_CAR_NBR = I_ED.STDNT_CAR_NBR
          AND I.ADM_APPL_NBR = I_ED.ADM_APPL_NBR
          AND I.APPL_PROG_NBR = I_ED.APPL_PROG_NBR
          AND I_ED.EFFDT <= SYSDATE)
    AND I.EFFSEQ =
        (SELECT MAX(I_ES.EFFSEQ) FROM PS_ADM_MC_VW I_ES
        WHERE I.EMPLID = I_ES.EMPLID
          AND I.ACAD_CAREER = I_ES.ACAD_CAREER
          AND I.STDNT_CAR_NBR = I_ES.STDNT_CAR_NBR
          AND I.ADM_APPL_NBR = I_ES.ADM_APPL_NBR
          AND I.APPL_PROG_NBR = I_ES.APPL_PROG_NBR
          AND I.EFFDT = I_ES.EFFDT)
     AND I.EMPLID = J.EMPLID
     AND I.ACAD_CAREER = J.ACAD_CAREER
     AND I.STDNT_CAR_NBR = J.STDNT_CAR_NBR
     AND I.ADM_APPL_NBR = J.ADM_APPL_NBR
     AND I.APPL_PROG_NBR = J.APPL_PROG_NBR
     AND J.EFFSEQ = I.EFFSEQ
     AND J.EFFDT = I.EFFDT
     AND I.INSTITUTION = :1
     AND I.ACAD_CAREER = :2
     AND I.PROG_ACTION NOT IN ('WADM','WAPP')
     AND I.EMPLID = K.EMPLID
     AND I.EMPLID = L.EMPLID
     AND I.ACAD_CAREER = L.ACAD_CAREER
     AND I.STDNT_CAR_NBR = L.STDNT_CAR_NBR
     AND L.INSTITUTION = I.INSTITUTION
     AND L.STRM BETWEEN :3 AND :4
     AND ( I.ADMIT_TYPE = :5
     OR :5 IS NULL)
     AND NOT EXISTS (SELECT M.EMPLID
  FROM PS_SRVC_IND_DATA M
  WHERE ( M.EMPLID = I.EMPLID
     AND M.INSTITUTION = I.INSTITUTION
     AND M.SRVC_IND_CD = 'TST'
     AND M.SRVC_IND_REASON = 'SKILL' ))
     AND ( NOT EXISTS (SELECT N.EMPLID
  FROM PS_STDNT_TEST_COMP N
  WHERE ( N.EMPLID = I.EMPLID
     AND N.TEST_ID = 'READ EXEMPT'
     AND N.TEST_COMPONENT IN ('B','E','Q','R','S','T','X') ))
     AND ( NOT EXISTS (SELECT O.EMPLID
  FROM PS_STDNT_TEST_COMP O
  WHERE ( O.EMPLID = I.EMPLID
     AND O.TEST_COMPONENT IN ('RAF','REF')
     AND O.SCORE_LETTER = 'PASS' ))
     AND EXISTS (SELECT P.EMPLID
  FROM PS_STDNT_TEST_COMP P
  WHERE ( P.EMPLID = I.EMPLID
     AND P.TEST_COMPONENT IN ('REF','RAF')
     AND P.SCORE_LETTER = 'FAIL' )))) )
UNION
SELECT S.LAST_NAME, S.FIRST_NAME, S.MIDDLE_NAME, Q.EMPLID, Q.ADMIT_TYPE, Q.ADMIT_TERM, Q.ACAD_PROG, R.ACAD_PLAN, Q.PROG_ACTION, S.PHONE, S.EMAIL_ADDR
  FROM PS_ADM_MC_VW Q, PS_ADM_APPL_PLAN R, PS_CU_BIO_VW S, PS_STDNT_CAR_TERM T
  WHERE ( Q.EFFDT =
        (SELECT MAX(Q_ED.EFFDT) FROM PS_ADM_MC_VW Q_ED
        WHERE Q.EMPLID = Q_ED.EMPLID
          AND Q.ACAD_CAREER = Q_ED.ACAD_CAREER
          AND Q.STDNT_CAR_NBR = Q_ED.STDNT_CAR_NBR
          AND Q.ADM_APPL_NBR = Q_ED.ADM_APPL_NBR
          AND Q.APPL_PROG_NBR = Q_ED.APPL_PROG_NBR
          AND Q_ED.EFFDT <= SYSDATE)
    AND Q.EFFSEQ =
        (SELECT MAX(Q_ES.EFFSEQ) FROM PS_ADM_MC_VW Q_ES
        WHERE Q.EMPLID = Q_ES.EMPLID
          AND Q.ACAD_CAREER = Q_ES.ACAD_CAREER
          AND Q.STDNT_CAR_NBR = Q_ES.STDNT_CAR_NBR
          AND Q.ADM_APPL_NBR = Q_ES.ADM_APPL_NBR
          AND Q.APPL_PROG_NBR = Q_ES.APPL_PROG_NBR
          AND Q.EFFDT = Q_ES.EFFDT)
     AND Q.EMPLID = R.EMPLID
     AND Q.ACAD_CAREER = R.ACAD_CAREER
     AND Q.STDNT_CAR_NBR = R.STDNT_CAR_NBR
     AND Q.ADM_APPL_NBR = R.ADM_APPL_NBR
     AND Q.APPL_PROG_NBR = R.APPL_PROG_NBR
     AND R.EFFSEQ = Q.EFFSEQ
     AND R.EFFDT = Q.EFFDT
     AND Q.INSTITUTION = :1
     AND Q.ACAD_CAREER = :2
     AND Q.PROG_ACTION NOT IN ('WADM','WAPP')
     AND Q.EMPLID = S.EMPLID
     AND Q.EMPLID = T.EMPLID
     AND Q.ACAD_CAREER = T.ACAD_CAREER
     AND Q.STDNT_CAR_NBR = T.STDNT_CAR_NBR
     AND T.INSTITUTION = Q.INSTITUTION
     AND T.STRM BETWEEN :3 AND :4
     AND ( Q.ADMIT_TYPE = :5
     OR :5 IS NULL)
     AND NOT EXISTS (SELECT U.EMPLID
  FROM PS_SRVC_IND_DATA U
  WHERE ( U.EMPLID = Q.EMPLID
     AND U.INSTITUTION = Q.INSTITUTION
     AND U.SRVC_IND_CD = 'TST'
     AND U.SRVC_IND_REASON = 'SKILL' ))
     AND ( NOT EXISTS (SELECT V.EMPLID
  FROM PS_STDNT_TEST_COMP V
  WHERE ( V.EMPLID = Q.EMPLID
     AND V.TEST_ID = 'WRIT EXEMPT'
     AND V.TEST_COMPONENT IN ('B','E','Q','R','S','T','X') ))
     AND ( NOT EXISTS (SELECT W.EMPLID
  FROM PS_STDNT_TEST_COMP W
  WHERE ( W.EMPLID = Q.EMPLID
     AND W.SCORE_LETTER = 'PASS'
     AND W.TEST_COMPONENT IN ('FINAL','WEF','WRF') ))
     AND EXISTS (SELECT X.EMPLID
  FROM PS_STDNT_TEST_COMP X
  WHERE ( X.EMPLID = Q.EMPLID
     AND X.TEST_COMPONENT IN ('WRF','WEF','FINAL')
     AND X.SCORE_LETTER = 'FAIL' )))) )
  ORDER BY 1, 2, 3
  • No labels