You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 7 Next »

The page in progress

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 query detail please see the link CU_BAR_AD_00006A_1 and CU_BAR_AD_00006B_2

Final Report Delivery with requirements:

---------

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_VWLAST_NAME, FIRST_NAME, MIDDLE_NAME, PHONE, EMAIL_ADDR
SCC_PERS_NI_QVWSSN
STDNT_TEST_COMPTEST ID, TEST DT, DATA SRCE, TOTAL SCORE, SG
SUB-QUEREIES:
 
STDNT_TEST_COMP
max (J.TEST_DT)
STDNT_GRPS_HISTB.EMPLID - Empl ID  (DOESN'T EXIST sg ('M1','M2'), NTMT, S100 )
STDNT_TEST_COMPBLANK (DOESN'T EXIST 'SMA3','MATHB' test component)
STDNT_TEST_COMPF.EMPLID - Empl ID (EXIST test id 'MATH EXEMPT')
STDNT_TEST_COMPI.EMPLID - Empl ID (EXIST test-com 'MAF', test s > 25)
STDNT_TEST_COMPG.EMPLID - Empl ID (EXIST test-com 'M1', test s > 45)
STDNT_TEST_COMPH.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)

 

 

  • No labels