Background:

Sarah Leobis wanted a report for Zicklin Undergraduate Accounting BBA Enrollment by term.

Greetings,

The Zicklin Dean's Office is currently working on the AACSB accreditation survey and require information via queries in CUNYfirst. We will require the following queries by Monday, April 20th, 2015 to complete the survey:


 Query 1: Enrollment

o Institution: BAR01

o Academic Level (e.g. Freshman, Lower Sophomore, Upper Sophomore, etc.)

o Academic Plan: ACC-BBA

o Term (Prompt to select term)

o Academic load

o Sex

o Citizenship

o Ethnicity

o Career


If you have any questions or if there is anything else needed, please let me know as soon as possible.

Thank you in advance.


Best regards,
Sarah


Final Report Delivery with requirements:


The query for Zicklin Enrollment CU_BAR_SR_00030_7 haS been shared with Sarah and Patrick. The query is working fine with accurate report. 

CU_BAR_SR_00030_7 Prompts are below: 

  • Institution: Bar01 
  • Term: pick any term 


 

Query Development:

Based on her request The CU_BAR_SR_00030_6 has been modified to CU_BAR_SR_00030_7.  The following steps have been followed to full fill the requirements:

Prompt:

Removed the prompt called Acad_Plan because they preferred the static data.

Criteria:

Added Acad_plan = 'ACC-BBA' to find only Acounting BBA student for a given term and see their status in report.

 

 

Query Output:

 

SQL Summery:

SELECT A.EMPLID, B.LAST_NAME, B.FIRST_NAME, B.MIDDLE_NAME, C.SEX, F.RESIDENCY, P.VISA_PERMIT_TYPE, A.ACAD_PROG, M.ACAD_PLAN, R.ACAD_SUB_PLAN, E.ACAD_PLAN, Q.ACAD_SUB_PLAN, A.ADMIT_TERM, D.UNT_TAKEN_PRGRSS, D.TOT_CUMULATIVE, D.CUM_GPA, decode ( G.ETHNIC_GROUP, '1', 'White ') ||

decode ( H.ETHNIC_GROUP, '2', 'Black ') ||

decode ( I.ETHNIC_GROUP, '3', 'Hispanic ') ||

decode ( J.ETHNIC_GROUP, '4', 'Asian ') ||

decode ( K.ETHNIC_GROUP, '5', 'AmerInd ') ||

decode ( L.ETHNIC_GROUP, '6', 'Non-Hispanic/Not-Specified '), B.PHONE, B.EMAIL_ADDR, A.ACAD_CAREER, D.ACADEMIC_LOAD, T.DESCR,T.COUNTRY,T.CITIZENSHIP_STATUS

  FROM PS_ACAD_PROG A, PS_CU_BIO_VW B, PS_SF_STDNT_PRS_VW C, PS_STDNT_CAR_TERM D, PS_SCC_ETH_GRP_VW G, PS_SCC_ETH_GRP_VW H, PS_SCC_ETH_GRP_VW I, PS_SCC_ETH_GRP_VW J, PS_SCC_ETH_GRP_VW K, PS_SCC_ETH_GRP_VW L, PS_ACAD_PLAN M, PS_ACAD_PLAN E, PS_RESIDENCY_OFF F, PS_SCC_VISA_P_QVW P, PS_ACAD_SUBPLAN Q, PS_ACAD_SUBPLAN R, PS_SCC_CITIZEN_QVW S, PS_CITIZEN_STS_TBL T

  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.INSTITUTION = :1

     AND A.ACAD_CAREER = 'UGRD'

     AND D.STRM = :2

     AND A.EMPLID = B.EMPLID

     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 A.EMPLID =  G.EMPLID (+)

     AND '1' =  G.ETHNIC_GROUP (+)

     AND A.EMPLID =  H.EMPLID (+)

     AND '2' =  H.ETHNIC_GROUP (+)

     AND A.EMPLID =  I.EMPLID (+)

     AND '3' =  I.ETHNIC_GROUP (+)

     AND A.EMPLID =  J.EMPLID (+)

     AND '4' =  J.ETHNIC_GROUP (+)

     AND A.EMPLID =  K.EMPLID (+)

     AND '5' =  K.ETHNIC_GROUP (+)

     AND A.EMPLID =  L.EMPLID (+)

     AND '6' =  L.ETHNIC_GROUP (+)

     AND A.EMPLID = M.EMPLID

     AND A.ACAD_CAREER = M.ACAD_CAREER

     AND A.STDNT_CAR_NBR = M.STDNT_CAR_NBR

     AND A.EFFSEQ = M.EFFSEQ

     AND A.EFFDT = M.EFFDT

     AND M.EMPLID =  E.EMPLID (+)

     AND M.ACAD_CAREER =  E.ACAD_CAREER (+)

     AND M.STDNT_CAR_NBR =  E.STDNT_CAR_NBR (+)

     AND M.EFFSEQ =  E.EFFSEQ (+)

     AND M.EFFDT =  E.EFFDT (+)

     AND M.ACAD_PLAN <>  E.ACAD_PLAN (+)

     AND A.EMPLID = F.EMPLID

     AND A.ACAD_CAREER = F.ACAD_CAREER

     AND F.INSTITUTION = A.INSTITUTION

     AND A.PROG_STATUS = 'AC'

     AND D.UNT_TAKEN_PRGRSS > 0

     AND M.ACAD_PLAN = 'ACC-BBA'

     AND M.PLAN_SEQUENCE = (SELECT MIN ( N.PLAN_SEQUENCE)

  FROM PS_ACAD_PLAN N

  WHERE N.EMPLID = M.EMPLID

     AND N.ACAD_CAREER = M.ACAD_CAREER

     AND N.STDNT_CAR_NBR = M.STDNT_CAR_NBR

     AND N.EFFDT = M.EFFDT

     AND N.EFFSEQ = M.EFFSEQ)

     AND F.EFFECTIVE_TERM = (SELECT MAX ( O.EFFECTIVE_TERM)

  FROM PS_RESIDENCY_OFF O

  WHERE O.EMPLID = F.EMPLID

     AND O.ACAD_CAREER = F.ACAD_CAREER

     AND O.INSTITUTION = F.INSTITUTION

     AND O.EFFECTIVE_TERM <= :2)

     AND A.EMPLID =  P.EMPLID (+)

     AND 'USA' =  P.COUNTRY (+)

     AND E.EMPLID =  Q.EMPLID (+)

     AND E.ACAD_CAREER =  Q.ACAD_CAREER (+)

     AND E.STDNT_CAR_NBR =  Q.STDNT_CAR_NBR (+)

     AND E.EFFSEQ =  Q.EFFSEQ  (+)

     AND E.ACAD_PLAN =  Q.ACAD_PLAN (+)

     AND E.EFFDT =  Q.EFFDT (+)

     AND M.EMPLID =  R.EMPLID (+)

     AND M.ACAD_CAREER =  R.ACAD_CAREER (+)

     AND M.STDNT_CAR_NBR =  R.STDNT_CAR_NBR (+)

     AND M.EFFSEQ =  R.EFFSEQ (+)

     AND M.ACAD_PLAN =  R.ACAD_PLAN (+)

     AND M.EFFDT =  R.EFFDT (+)

     AND A.EMPLID =  S.EMPLID (+)

     AND 'USA' =  S.COUNTRY (+)

     AND S.COUNTRY =  T.COUNTRY (+)

     AND S.CITIZENSHIP_STATUS =  T.CITIZENSHIP_STATUS (+)

  ORDER BY 2, 3, 4

  • No labels