Background:

Daniel Battista has been requested to add some more fields and wanted to remove duplication from existed report.  Based on his criteria I have created a new query while kept the original requirements and added more to meet the current requirements to meet their goal for SPA.  For detail email information please see the kace: //helpdesk.baruch.cuny.edu the ticket number is 48970

As noted this request should have been ticketed by the Baruch Help Desk and assigned for feedback here instead of going to CUNY Central Help Desk.
 
In any case you are requesting a modification of the query CU_BAR_SR_00054_5.
 
From your message:
Ethnicity; including the Hispanic checkbox field, plus the associated Ethic Group(s).  See the screen shot attached for reference.   I have some concerns about how this information might populate and display appropriately.  For example, someone may be Hispanic, select a subgroup within Hispanic (right next to the checkbox) and also indicate their race as White, Black, and/or Native American.  I’m curious how that might work.
 
Can you pass along the screen shot?
There are some other private queries which include ethnicity and would include when multiple listed. See private queries here for some examples - CU_BAR_SR_00027, CU_BAR_SR_00030_2, CU_BAR_SR_00040
Example of Ethnicities value from CU_BAR_SR_00040 – includes multiple ethnicities
 
Gender (Male, Female, Unknown) – pretty straight forward
 
Citizenship; Country and Citizenship Status (see attached for reference).  We currently have two fields in this report (appear as column AB and AC in a generated Excel file, so the 28th and 29th fields) that both have headings that appear as “Country”- can you tell me what fields these are exactly- what information this captures? Do one of these fields already represent Citizenship? 
 
Notes for CU_BAR_SR_00054_4 (https://www.baruch.cuny.edu/confluence/x/rQE5AQ) indicate addition of Country fields for dual citizenship, but running this query for 1162 all results reflect “USA” for both columns – seems strange.
 
Can you provide an example of a student with country of citizenship other than USA?

Final Report Delivery with requirements:

 The report does include followings:   

 

 

  • EmplID
  • LastName
  • FirstName
  • MiddleName
  • Program
  • Plan
  • Sub-Plan
  • TermCredits
  • AdmitTerm
  • CampusEmail
  • PreferredPhone
  • ExpGradTerm
  • ChkoutStat
  • TermGPA
  • CumGPA
  • Status
  • MailingAddress1
  • Address2
  • City
  • State
  • Postal
  • Residency
  • TotCompCredits
  • TotAttCredits
    Newly added below:
  • Sex
  • AcadLoad(Full/Parttime)
  • AcadLoad
  • ComplTerm
  • PrimProg
  • Country
  • CitizenshipStatus
  • Country
  • CitizenshipStatus
  • Ethnicity
  • VisPmtType

 

 

CU_BAR_SR_00054_6 Prompts are below:   

  •  Term: pick any term 

 

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
ACAD_PROG
EMPLID, ADMIT TERM,EXP_GRAD_TERM, DEGR_CHKOUT_STAT, PROG_STATUS
ACAD_PLANACAD_PLAN
ACAD_SUBPLANACAD_SUB_PLAN
STDNT_CAR_TERMUNT_TAKEN_PRGRSS, TOT_TAKEN_PRGRSS, TOT_PASSD_PRGRSS, ACAD_LOAD_APPR , ACADEMIC_LOAD, ACAD_PROG_PRIMARY
SCC_EMAIL_QVW 'CAMP' mail thru expression
 ADDRESSES4_VW ADDRESS1, ADDRESS2, CITY, STATE, POSTAL
 RESIDENCY_OFF RESIDENCY
SF_STDNT_PRS_VW SEX - Gender
SCC_PERS_PH_QVWPHONE
NAME_CURR_VW LAST_NAME, FIRST_NAME, MIDDLE_NAME
SCC_CITIZEN_QVW COUNTRY (USA)
SCC_CITIZEN_QVW COUNTRY (OTHER)

CITIZEN_STS_TBL 

DESCR (USA)
CITIZEN_STS_TBL DESCR (OTHER)
SCC_ETH_GRP_VW 'ETHNICITY' thru expression
SCC_VISA_P_QVWVisaPmtType


 Expressions:

ExpressionExplanation
'CAMP'Find campus email
'MAIL'Find other email
'Y'Find preferred phone number
'PRI'Find primary email
'USA'Find USA status /other than USA
'USA'Find visa permit type status

LISTAGG(CASE WHEN P.ETHNIC_GROUP IN ('1') THEN 'WHITE'

    WHEN P.ETHNIC_GROUP IN ('2') THEN 'BLACK'

    WHEN P.ETHNIC_GROUP IN ('3') THEN 'HISPANIC'

WHEN P.ETHNIC_GROUP IN ('4') THEN 'ASIAN'

WHEN P.ETHNIC_GROUP IN ('5') THEN 'AMERIND'

WHEN P.ETHNIC_GROUP IN ('6') THEN 'NON-HISPANIC/NOT-SPECIFIED'

when P.ETHNIC_GROUP is null then 'Not Provided'

    ELSE 'OTHER' END, ', ') WITHIN GROUP (ORDER BY

  CASE WHEN P.ETHNIC_GROUP IN ('1') THEN 'WHITE'

WHEN P.ETHNIC_GROUP IN ('2') THEN 'BLACK'

    WHEN P.ETHNIC_GROUP IN ('3') THEN 'HISPANIC'

WHEN P.ETHNIC_GROUP IN ('4') THEN 'ASIAN'

WHEN P.ETHNIC_GROUP IN ('5') THEN 'AMERIND'

WHEN P.ETHNIC_GROUP IN ('6') THEN 'NON-HISPANIC/NOT-SPECIFIED'

when P.ETHNIC_GROUP is null then 'Not Provided'

ELSE 'OTHER' END)

Find Ethnicity


Prompt:

Term  for Current or Prior have been mandatory for an user to input in order to retrieve data.

Criteria:

ACAD_PROG_STATUS has not been included 'AC' because the report is based on everyone who is active and enrolled in a MPA, MSED or ADVCT program.  The sub-query include ' RESIDENCY_OFF ' record because according to the requirement we need to find latest mailing address information for the report.  Different kind of email address have been added to the query based on requirements.  Student can have one or more ethnic background.  Therefore, Ethnicity function has been custom made and calling through expression into report. 'Gender', 'Country', 'Citizenship Status', 'ACAD Load(Full/Part time)', ' Completion term' and 'Primary Program' fields also newly added to the query.

The image of CU_BAR_SR_00054_6:

Query SQL

SELECT A.EMPLID, B.LAST_NAME, B.FIRST_NAME, B.MIDDLE_NAME, A.ACAD_PROG, C.ACAD_PLAN, D.ACAD_SUB_PLAN, E.UNT_TAKEN_PRGRSS, A.ADMIT_TERM, F.EMAIL_ADDR, K.PHONE, A.EXP_GRAD_TERM, A.DEGR_CHKOUT_STAT, E.CUR_GPA, E.CUM_GPA, A.PROG_STATUS, G.ADDRESS1, G.ADDRESS2, G.CITY, G.STATE, G.POSTAL, H.RESIDENCY, E.TOT_PASSD_PRGRSS, E.TOT_TAKEN_PRGRSS, J.SEX, E.ACAD_LOAD_APPR, E.ACADEMIC_LOAD, A.COMPLETION_TERM, E.ACAD_PROG_PRIMARY, L.COUNTRY, N.DESCR, M.COUNTRY, O.DESCR, LISTAGG(CASE WHEN  P.ETHNIC_GROUP IN ('1') THEN 'WHITE'

    WHEN  P.ETHNIC_GROUP IN ('2') THEN 'BLACK'

    WHEN  P.ETHNIC_GROUP IN ('3') THEN 'HISPANIC'

WHEN  P.ETHNIC_GROUP IN ('4') THEN 'ASIAN'

WHEN  P.ETHNIC_GROUP IN ('5') THEN 'AMERIND'

WHEN  P.ETHNIC_GROUP IN ('6') THEN 'NON-HISPANIC/NOT-SPECIFIED'

when  P.ETHNIC_GROUP is null then 'Not Provided'

    ELSE 'OTHER' END, ', ') WITHIN GROUP (ORDER BY

  CASE WHEN  P.ETHNIC_GROUP IN ('1') THEN 'WHITE'

WHEN  P.ETHNIC_GROUP IN ('2') THEN 'BLACK'

    WHEN  P.ETHNIC_GROUP IN ('3') THEN 'HISPANIC'

WHEN  P.ETHNIC_GROUP IN ('4') THEN 'ASIAN'

WHEN  P.ETHNIC_GROUP IN ('5') THEN 'AMERIND'

WHEN  P.ETHNIC_GROUP IN ('6') THEN 'NON-HISPANIC/NOT-SPECIFIED'

when  P.ETHNIC_GROUP is null then 'Not Provided'

ELSE 'OTHER' END), Q.VISA_PERMIT_TYPE, TO_CHAR(SYSDATE,'YYYY-MM-DD'), TO_CHAR(SYSDATE,'YYYY-MM-DD'), TO_CHAR(SYSDATE,'YYYY-MM-DD'),N.COUNTRY,N.CITIZENSHIP_STATUS,O.COUNTRY,O.CITIZENSHIP_STATUS

  FROM PS_ACAD_PROG A, PS_ACAD_PLAN C, PS_ACAD_SUBPLAN D, PS_STDNT_CAR_TERM E, PS_SCC_EMAIL_QVW F, PS_ADDRESSES4_VW G, PS_RESIDENCY_OFF H, PS_SF_STDNT_PRS_VW J, PS_SCC_PERS_PH_QVW K, PS_NAME_CURR_VW B, PS_SCC_CITIZEN_QVW L, PS_SCC_CITIZEN_QVW M, PS_CITIZEN_STS_TBL N, PS_CITIZEN_STS_TBL O, PS_SCC_ETH_GRP_VW P, PS_SCC_VISA_P_QVW Q

  WHERE ( A.EFFDT =

        (SELECT MAX(A_ED.EFFDT) FROM PS_ACAD_PROG 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_ED.EFFDT <= SYSDATE)

    AND A.EFFSEQ =

        (SELECT MAX(A_ES.EFFSEQ) FROM PS_ACAD_PROG 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.EFFDT = A_ES.EFFDT)

     AND A.ACAD_CAREER = 'GRAD'

     AND A.INSTITUTION = 'BAR01'

     AND A.ACAD_PROG IN ('ADVCT','MPA','MSED')

     AND A.EMPLID = C.EMPLID

     AND A.ACAD_CAREER = C.ACAD_CAREER

     AND A.STDNT_CAR_NBR = C.STDNT_CAR_NBR

     AND A.EFFSEQ = C.EFFSEQ

     AND A.EFFDT = C.EFFDT

     AND C.EMPLID =  D.EMPLID (+)

     AND C.ACAD_CAREER =  D.ACAD_CAREER (+)

     AND C.STDNT_CAR_NBR =  D.STDNT_CAR_NBR (+)

     AND C.EFFSEQ =  D.EFFSEQ (+)

     AND C.ACAD_PLAN =  D.ACAD_PLAN (+)

     AND C.EFFDT =  D.EFFDT (+)

     AND A.EMPLID = E.EMPLID

     AND A.ACAD_CAREER = E.ACAD_CAREER

     AND A.STDNT_CAR_NBR = E.STDNT_CAR_NBR

     AND E.INSTITUTION = A.INSTITUTION

     AND E.STRM = :1

     AND A.EMPLID =  F.EMPLID (+)

     AND 'CAMP' =  F.E_ADDR_TYPE (+)

     AND A.EMPLID =  G.EMPLID (+)

     AND 'MAIL' =  G.ADDRESS_TYPE (+)

     AND A.EMPLID = H.EMPLID

     AND A.ACAD_CAREER = H.ACAD_CAREER

     AND H.INSTITUTION = A.INSTITUTION

     AND H.EFFECTIVE_TERM = (SELECT MAX ( I.EFFECTIVE_TERM)

  FROM PS_RESIDENCY_OFF I

  WHERE ( I.EMPLID = H.EMPLID

     AND I.ACAD_CAREER = H.ACAD_CAREER

     AND I.INSTITUTION = H.INSTITUTION

     AND I.EFFECTIVE_TERM <= :1 ))

     AND A.EMPLID = J.EMPLID

     AND A.EMPLID =  K.EMPLID (+)

     AND 'Y' =  K.PREF_PHONE_FLAG (+)

     AND A.EMPLID =  B.EMPLID (+)

     AND 'PRI' =  B.NAME_TYPE (+)

     AND A.EMPLID =  L.EMPLID (+)

     AND 'USA' =  L.COUNTRY (+)

     AND A.EMPLID =  M.EMPLID (+)

     AND 'USA' <>  M.COUNTRY (+)

     AND L.COUNTRY =  N.COUNTRY (+)

     AND L.CITIZENSHIP_STATUS =  N.CITIZENSHIP_STATUS (+)

     AND M.COUNTRY =  O.COUNTRY (+)

     AND M.CITIZENSHIP_STATUS =  O.CITIZENSHIP_STATUS (+)

     AND A.EMPLID =  P.EMPLID (+)

     AND A.EMPLID =  Q.EMPLID (+)

     AND 'USA' =  Q.COUNTRY (+) )

  GROUP BY  A.EMPLID,  B.LAST_NAME,  B.FIRST_NAME,  B.MIDDLE_NAME,  A.ACAD_PROG,  C.ACAD_PLAN,  D.ACAD_SUB_PLAN,  E.UNT_TAKEN_PRGRSS,  A.ADMIT_TERM,  F.EMAIL_ADDR,  K.PHONE,  A.EXP_GRAD_TERM,  A.DEGR_CHKOUT_STAT,  E.CUR_GPA,  E.CUM_GPA,  A.PROG_STATUS,  G.ADDRESS1,  G.ADDRESS2,  G.CITY,  G.STATE,  G.POSTAL,  H.RESIDENCY,  E.TOT_PASSD_PRGRSS,  E.TOT_TAKEN_PRGRSS,  J.SEX,  E.ACAD_LOAD_APPR,  E.ACADEMIC_LOAD,  A.COMPLETION_TERM,  E.ACAD_PROG_PRIMARY,  L.COUNTRY,  N.DESCR,  M.COUNTRY,  O.DESCR,  Q.VISA_PERMIT_TYPE,N.COUNTRY,N.CITIZENSHIP_STATUS,O.COUNTRY,O.CITIZENSHIP_STATUS

  ORDER BY 2, 3, 4

  • No labels